Datetime2 and datetimeoffset Data Types of datetime Data Types

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