Here we will learn DateTime datatypes in SQL server or date and time datatypes in the SQL server.
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
Please check the following table for more details regarding the date and time data types in SQL Server.
Data Type | Description | Storage 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 |
Please check the following table for date and time data type example formats in sql server.
Data Type | Example 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 |