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
2017-04-15, 910👍, 0💬
Popular Posts:
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
How To Count Duplicated Values in a Column in SQL Server? If you have a column with duplicated value...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...