Collections:
Date and Time Data Types in SQL Server Transact-SQL
What are date and time data types supported in SQL Server Transact-SQL?
✍: FYIcenter.com
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
2017-04-15, 1584🔥, 0💬
Popular Posts:
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL? Since Unicode ch...