The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are
- DATE
- TIME
- DATETIME2
- DATETIMEOFFSE
DATE Data Type
| Property | Value |
|---|---|
| Syntax | date |
| Usage | DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date ) |
| Default string literal format | YYYY-MM-DD |
| Range | 0001-01-01 through 9999-12-31 January 1, 1 A.D. through December 31, 9999 A.D. |
| Element ranges | YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month. |
| Character length | 10 positions |
| Precision, scale | 10, 0 |
| Storage size | 3 bytes, fixed |
| Storage structure | 1, 3-byte integer stores date. |
| Accuracy | One day |
| Default value | 1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset. |
| Calendar | Gregorian |
| User-defined fractional second precision | No |
| Time zone offset aware and preservation | No |
| Daylight saving aware | No |
TIME Datatype
| Property | Value |
|---|---|
| Syntax | time [ (fractional second precision) ] |
| Usage | DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) ) |
| fractional seconds precision | Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. The default fractional precision is 7 (100ns). |
| Usage | DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) ) |
| Default string literal format | hh:mm:ss[.nnnnnnn] |
| Range | 00:00:00.0000000 through 23:59:59.9999999 |
| Element ranges | hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. |
| Character length | 8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn) |
| Precision, scale (user specifies scale only) | Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision time(16,7)57 time(0)(8,0)30-2 time(1)(10,1)30-2 time(2)(11,2)30-2 time(3)(12,3)43-4 time(4)(13,4)43-4 time(5)(14,5)55-7 time(6)(15,6)55-7 time(7)(16,7)55-7 |
| Storage size | 5 bytes, fixed, is the default with the default of 100ns fractional second precision. |
| Accuracy | 100 nanoseconds |
| Default value | 00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset. |
| User-defined fractional second precision | Yes |
| Time zone offset aware and preservation | No |
| Daylight saving aware | No |
DATETIME2 Data Type
| Property | Value |
|---|---|
| Syntax | datetimeoffset [ (fractional seconds precision) ] |
| Usage | DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) ) |
| Default string literal formats (used for down-level client) | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data. |
| Date range | 0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D. |
| Time range | 00:00:00 through 23:59:59.9999999 |
| Time zone offset range |
|
| Element ranges | YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59. |
| Character length | 26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm) |
| Precision, scale | Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7 |
| Storage size | 10 bytes, fixed is the default with the default of 100ns fractional second precision. |
| Accuracy | 100 nanoseconds |
| Default value | 1900-01-01 00:00:00 00:00 |
| Calendar | Gregorian |
| User-defined fractional second precision | Yes |
| Time zone offset aware and preservation | Yes |
| Daylight saving aware | No |
DATETIMEOFFSET Datatype
| Property | Value |
|---|---|
| Syntax | datetimeoffset [ (fractional seconds precision) ] |
| Usage | DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) ) |
| Default string literal formats | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] |
| Date range | 0001-01-01 through 9999-12-31 January 1,1 A.D. through December 31, 9999 A.D. |
| Time range | 00:00:00 through 23:59:59.9999999 |
| Time zone offset range |
|
| Element ranges | YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59. |
| Character length | 26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm) |
| Precision, scale | Specified scaleResult (precision, scale)Column length (bytes)Fractional seconds precision datetimeoffset(34,7)107 datetimeoffset(0)(26,0)80-2 datetimeoffset(1)(28,1)80-2 datetimeoffset(2)(29,2)80-2 datetimeoffset(3)(30,3)93-4 datetimeoffset(4)(31,4)93-4 datetimeoffset(5)(32,5)105-7 datetimeoffset(6)(33,6)105-7 datetimeoffset(7)(34,7)105-7 |
| Storage size | 10 bytes, fixed is the default with the default of 100ns fractional second precision. |
| Accuracy | 100 nanoseconds |
| Default value | 1900-01-01 00:00:00 00:00 |
| Calendar | Gregorian |
| User-defined fractional second precision | Yes |
| Time zone offset aware and preservation | Yes |
| Daylight saving aware | No |
