Here are some notes on "SQL Server 2008 New Date and Time Types" I took while attending an advanced class on SQL Server taught by Paul Nielsen (from http://www.sqlserverbible.com/).
Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.
New date and time types
- Date – 3 bytes, accurate to day
- Time [0-7] – 3 to 5 bytes, accurate to 100 nanoseconds
- DateTime2 [0-7] – 6 to 8 bytes, accurate to 100 nanoseconds
- DateTimeOffset [0-7] – 8 to 10 bytes, accurate to 100 nanoseconds - Specify time zone offset - Compared, sorted and indexed as UTC
New date and time functions
- DatePart /DateNum arguments: Microsecond, Nanosecond, Tzoffset, ISO_Week
- ToDataTimeOffSet, SysDateTime, SysDateTimeOffset, SysUTCDateTime, GetUTCDate
- SysDateTime, SysDateTimeOffSet, SysUTCDateTime, ToDateTimeOffset, SwitchOffSet
- See http://technet.microsoft.com/en-us/library/ms186724.aspx
- In new types, time is more precise
- GetDate() is deprecated, use SysDateTime()
- Can’t add 1 to DateTime2 to increment to the next day (as you could with DateTime)
- Now yyyy-mm-dd is always month first. Used to depend on settings previously
- No notion of daylight savings, consider using types that include the offset
- Careful with expressions like WHERE mydate BETWEEN '20090101' AND '20090101 23:59:59.997'
This is assuming a specific precision for the time. You really shouldn't do that!
Consider using WHERE CONVERT(date, mydate)='20090101' - which does use the index :-O