date and time
At last we’ve got date and time data types separately. In Sql Server 2008 there are several new types worth looking at. These are: time, date, datetime2 and datetimeoffset.
time
Represents a time of a day in 24-hour format. We can specify how many fractional seconds we want to store. Valid range is from 0 to 7, default is 7. Storage size depends on the scale used and is between 3 and 5 bytes. For example:
SELECT CAST(GETDATE() AS TIME(4));
date
Represents a date. The valid range was expanded and is now 0001-01-01 through 9999-12-31. Storage size is 3 bytes.
SELECT CAST(GETDATE() AS DATE);
datetime2
Represents a combination of date and time. It is a datetime successor with larger range and fractional seconds precision. Storage size is between 6 and 8 bytes.
SELECT CAST(GETDATE() AS DATETIME2(5));
datetimeoffset
Represents a data and time with time zone awareness. We specify time zone offset using [+|-] hh:mm, where hh is two digits range from 00 to 14. Storage size between 8 and 10 bytes. For instance
SELECT CAST('2007-08-11 13:08:11' AS DATETIMEOFFSET(5));
returns 2007-08-11 13:08:11.00000 +00:00.
SELECT DATEDIFF( HOUR, CAST('2007-08-11 13:08:11 +01:00' AS DATETIMEOFFSET(5)), CAST('2007-08-11 13:08:11' AS DATETIME2(5)));
returns 1.