You’d think that with 10,000 years of date- and time-keeping under our belts, it would be easy to keep track of dates and times in a modern-day database. It’s a little trickier than you might think, according to The Death of DateTime?, an article in Bart Duncan’s SQL Weblog.
The gist of the article is pretty simple: if you’re using SQL Server 2008 and want to store dates and times unambiguously, use the datetimeoffset type (introduced in SQL Server 2008) rather than the traditional datetime.
Why? Because datetimeoffset is datetime with these key differences:
- The time value is stored internally in an unambiguous UTC format
- The local time zone offset is stored along with the UTC time
- It is capable of storing more precise times than
datetime
Duncan recommends that if you’re storing data in SQL Server 2008, you should almost always store date-and-time values in datetimeoffset rather than datetime. It’s a good idea; I’d go even farther and suggest that if you’re programming using .NET 3.5, you should make use of the corresponding DateTimeOffset type instead of DateTime. You can read more about .NET 3.5’s DateTimeOffset type in this entry in Dan Rigsby’s blog titled DateTime vs. DateTimeOffset in .NET.
When might you want to use datetime? Duncan suggests that you should use it in those rare cases when you want to store time ambiguously. The example he provides is: “if you wanted a column to record the fact that all stores in a chain should open at 8:00am local time (whatever the local time zone may be), you should use datetime.”