What are date and time data types supported in SQL Server Transact-SQL?

Date and time data types are used to hold dates and times.

There are 6 date and time data types supported in SQL Server Transact-SQL:

1. DATETIME - Use to hold date and times with a large precision using 8-byte storages: 4 bytes for the date part and 4 bytes for the time part. DATETIME values are in the range of January 1, 1753 to December 31, 9999 with a precision of 3.33 milliseconds.

In other words, DATETIME should be used to record date and time at the 0.01 second (10 millisecond) level, not the millisecond level.

2. SMALLDATETIME - Use to hold date and times with a small precision using 4-byte storages: 2 bytes for the date part and 2 bytes for the time part. SMALLDATETIME values are in the range of January 1, 1900 to June 6, 2079 with a precision of 1 minute.

In other words, SMALLDATETIME should be used to record date and time at the minute level, not the second level.

3. DATE - Use to hold dates in the range of 0001-01-01 to 9999-12-31.

4. TIME - Use to hold times with different scales specified in the format of TIME(s), where s is the scale of the fractional part of a second.

5. DATETIMEOFFSET - Used to hold dates and times with timezone offsets with different scales specified in the format of DATETIMEOFFSET(s), where s is the scale of the fractional part of a second.

6. DATETIME2 - Used to hold dates and times with different scales specified in the format of DATETIME2(s), where s is the scale of the fractional part of a second.

Here are some good examples of date and time values:

PRINT '2107-05-19 22:52:51.607'; -- DATETIME
PRINT '2107-05-19 22:52:00'; -- SMALLDATETIME
PRINT '2107-05-19 22:52:00.1234567'; -- DATETIME2

⇒ Entering Date and Time Values in SQL Server

⇐ Differences of DECIMAL and FLOAT in SQL Server

⇑ Variables and Data Types in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials