Datetime Data Types in SQL Server

Here we will learn DateTime datatypes in SQL server or date and time datatypes in the SQL server.

SQL Date and Time Data Types

In sql, datetime data types are used to store a date or date and time data in particular column. We have a different type of date and time data types are available in sql server, those are

 

  • smalldatetime
  • datetime
  • datetime2
  • date
  • time
  • datetimeoffset

Please check the following table for more details regarding the date and time data types in SQL Server.

 

Data TypeDescriptionStorage Size
smalldatetime We can store values From January 1, 1900 to June 6, 2079, and it’s having an accuracy of 1 minute 4 Bytes
datetime We can store values from January 1, 1753, to December 31, 9999 and it’s having an accuracy of 3.33 milliseconds 8 Bytes
datetime2 We can store values from January 1, 0001 to December 31, 9999 and it’s having accuracy of 100 nanoseconds 6 - 8 Bytes
date We can store only date format like May 05, 1986, and the range is from January 1, 0001 to December 31, 9999 3 Bytes
time We can store only time format like 12.00 PM 3 - 5 Bytes
datetimeoffset It’s same as datetime2 with the addition of a time zone offset 8 - 10 Bytes

SQL DateTime Datatype Example Formats

Please check the following table for date and time data type example formats in sql server.

 

Data TypeExample Format
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
date 2007-05-08
time 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 12:15