SQL Server 2008 New Date and Time Types

  • Article

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 https://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 https://technet.microsoft.com/en-us/library/ms186724.aspx 

Considerations

  • 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

Read more at https://technet.microsoft.com/en-us/library/cc721270.aspx
See also https://www.karaszi.com/SQLServer/info_datetime.asp