The new date and time types in SQL Server 2008 are datetime2 and datetimeoffset.
Datetime2 data type, It is similar to the datetime type, but its precision is relatively high. It can be precise to the last 7 digits (100ns) after the decimal point. Its syntax is datetime2 (n ). Example:
Declare @ Dt As Datetime2 ( 5 )
Set @ Dt = Getdate ()
Select @ Dt
Datetimeoffset Data TypeThe Time Zone offset is added. The time zone offset is [+ |-] hh: Mm. HH is the two-digit number from 00 to 14, indicating the number of hours at the time zone offset. Mm is the two-digit number from 00 to 59, indicating the number of additional minutes of the time zone offset. The time format can be up to 100 milliseconds. The required + or-symbol indicates whether to add or subtract the time zone offset in UTC (Universal Coordinated Time or Greenwich Mean Time) to obtain the local time. Example:
Declare @ Dt As Datetimeoffset ( 8 )
Set @ Dt = ' 2008-08-08 08:08:08. 0 + 8: 00 '
Select @ Dt
Mappings with CLR data types:
SQL data type |
. NET Framework type |
System. Data. sqldbtype |
System. Data. dbtype |
Date |
System. datetime |
Date |
Date |
Time |
System. timespan |
Time |
Time |
Datetime2 |
System. datetime |
Datetime2 |
Datetime2 |
Datetimeoffset |
System. datetimeoffset |
Datetimeoffset |
Datetimeoffset |
Datetime |
System. datetime |
Datetime |
Datetime |
Smalldatetime |
System. datetime |
Datetime |
Datetime |