Introduction
From the beginning, Microsoft SQL Server DBAs have longed for two different data types, where you could store time and date separately. The long wait is over. In all of the older versions, we had only one data type that could store Datetime data type, and it stored both the value of the date and the value of the time.
Finally, in Microsoft SQL Server 2008, Microsoft is introducing a set of new data types for storing date, time and both date and time together. The new data types store more data as well, which means you can store dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the fraction of 9999999.
There are four DATETIME data types newly introduced in SQL Server 2008. They are
DATE, TIME, DATETIMEOFFSET and DATETIME2.
1) DATE Data Type
The DATE data type stores only the date value.
The DATE data type returns data in the form of year, Month and Day. However, the format in which the date is returned is determined by the current language setting in SQL Server.
The range for the DATE data type is from 0001-01-01 through 9999-12-31.
When you specify a datetime value, SQL Server automatically converts that value into the DATE data type, which means that only the date portion is stored.
Example:
DECLARE @Date_Type DATE
SET @Date_Type = '2011-05-17 05:29:15.1234567'
SELECT @Date_Type
Result:
2011-05-17
2) TIME Data type
The TIME data type stores only the time value.
The TIME data type returns data in the form of hour, minute, second and fractional second.
The fractional part of the seconds can support up to 7 decimal places.
The precision of the TIME data type supports a range of 00:00:00.0000000 through 23:59:59.9999999.
When you specify the TIME datatype in a Transact-SQL statement, you can specify the precision of the stored values by including the appropriate number within parentheses.
For example, to specify a precision of 7, you would specify TIME(7).
For a precision of 5, you would specify TIME(5), and so on.
If you do not specify the precision, 7 are assumed.
Example:
DECLARE @Time_Type TIME
SET @Time_Type = '2011-05-17 05:29:15.1234567'
SELECT @Time_Type
Result:
05:29:15.1234567
You can also specify a different precision when converting the data
DECLARE @DateTimeOffset_Type TIME
DECLARE @Time_Type TIME
SET @DateTimeOffset_Type = '2011-05-17 05:29:15.1234567'
SET @Time_Type = CAST(@DateTimeOffset_Type AS TIME(5))
SELECT @Time_Type
Result:
05:29:15.1234600
Notice that the fractional seconds have been rounded up.
The original fractional seconds were .1234567. However, if you specify a precision of 5, SQL Server will round up the "67" fractional part, returning .1234600 in the results, rather than .1234500.
When inserting data into a TIME column, you can specify a time value or a datetime value as shown in the following INSERT statements:
INSERT INTO TableName (ID, Time_DataType)
VALUES ('1004, 05:23:10.1234567'); --Only Time
INSERT INTO TableName (ID, Time_DataType)
VALUES (1005, '2011-05-17 05:23:10.1234567'); --Date & Time
In both cases, only the time is inserted into the Time_DataType column.
3) DATETIME2 Data Type
The DATETIME2 data type stores the both date and time value.
The data type DATETIME2 is the combination of the data type DATE and TIME.
DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value 00:00:00.0000000 to 23:59:59.9999999.
Example:
DECLARE @DateTime2_Type DATETIME2
SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')
SELECT @DateTime2_Type
Result:
0001-01-01 16:14:00.1234567
We could limit the precision like (Precision value range 0-7),
DECLARE @DateTime2_Type DATETIME2(4)
SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')
SELECT @DateTime2_Type
Result:
0001-01-01 16:14:00.1235
Notice that the fractional seconds have been rounded up.
4) DATETIMEOFFSET Data type
The DATETIMEOFFSET data type stores the date, time and time-zone offset value.
The DATETIMEOFFSET data type is nearly identical to the DATETIME2 data type, except that a DATETIMEOFFSET value includes one important addition that is a time-zone offset value.
The offset value represents the number of hours and minutes before or after Coordinated Universal Time (UTC).
A positive number indicates the amount of time to add to the UTC to determine the local time. (+05:30).
A negative number indicates the amount of time to subtract from the UTC to determine the local time. (-05:30)
The Date range is between 0001-01-01 and 9999-12-31.
The Time Range is between 00:00:00 and 23:59:59.9999999.
The Offset range is between -14:00 through +14:00.
Example:
DECLARE @DateTimeOffset_Type DATETIMEOFFSET
SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')
SELECT @DateTimeOffset_Type
Result:
0001-01-01 16:14:00.1234567 +05:30
You can also set the precision of the data type, like
DECLARE @DateTimeOffset_Type DATETIMEOFFSET(4)
SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')
SELECT @DateTimeOffset_Type
Result:
0001-01-01 16:14:00.1235 +05:30
Summary:
Data type | Format | Range | Accuracy | Storage size (bytes) | User-defined fractional second precision | Time zone offset |
TIME | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 to 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | Yes | No |
DATE | YYYY-MM-DD | 00001-01-01 to 9999-12-31 | 1 day | 3 | No | No |
SMALLDATETIME | YYYY-MM-DD hh:mm:ss | 1900-01-01 to 2079-06-06 | 1 minute | 4 | No | No |
DATETIME | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 to 9999-12-31 | 0.333 second | 8 | No | No |
DATETIME2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 | Yes | No |
DATETIMEOFFSET | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 00001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 | Yes | Yes |
No comments:
Post a Comment