Two of the most welcome of these new types are the new date and time data types. These new data types allow you to store date-only and time-only values. In previous versions of SQL Server, the datetime and smalldatetime data types were the only available types for storing date or time values, and they always store both the date and time. This made date-only or time-only comparisons tricky at times because you always had to account for the other component. In addition, the datetime stored date values range only from 1/1/1753 to 12/31/9999, with accuracy only to 3.33 milliseconds. The smalldatetime stored date values range only from 1/1/1900 to 6/6/2079, with accuracy of only 1 minute.
The new date data type stores only the date component without the time component, and stores date values ranging from 1/1/0001 to 12/31/9999.
The new time data type stores only the time component with accuracy that can be specified down to seven decimal places (100 nanoseconds). The default is seven decimal places.
The datetime2 data type stores both date and time components, similar to datetime, increases the range of allowed values to 1/1/0001 to 12/31/9999, also with accuracy down to seven decimal places (100 ns). The default precision is seven decimal places.
The datetimeoffset data type also stores both date and time components just like datetime2, but includes the time zone offset from Universal Time Coordinates (UTC). The time zone offset ranges from -14:00 to +14:00.
Listing 1 demonstrates the use of some of the new data types and functions. Notice the difference in the specified decimal precision returned for the time values.
When you are specifying a time zone value for the SWITCHOFFSET or TODATETIMEOFFSET offset functions, the value can be specified as an integer value representing the number of minutes of offset or as a time value in hh:mm format. The range of allowed values is +14 hours to -13 hours.
When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly. For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value (to the persistent time zone offset). For example, when the datetimeoffset(3) value, 2006-10-21 12:20:20.999 -8:00, is converted to time(3), the result is 12:20:20.999, not 20:20:20.999(UTC).
If you convert from a higher-precision time value to a lower-precision value, the conversion is permitted, and the higher-precision values are truncated to fit the lower precision type.