I've been working a lot with the datetimeoffset data type recently so today's SQLServerCentral.com article The SQL Server 2008 Datetimeoffset Data Type caught my attention. It's a good read if you're unfamiliar with datetimeoffset, as is The Death of DateTime? from Bart Duncan's blog.
Both articles fall short of mentioning one downside of datetimeoffset: while it's easy to switch offsets, it's not easy to switch time zones without understanding the nuances of Daylight Saving Time (though to be fair it's not easy with any other datetime format in SQL Server either).
For example, if I want to convert 2011-03-12 07:30:00.0000000 +00:00 and 2011-03-14 07:30:00.0000000 +00:00 to Eastern time I need to understand that the offset for the 12th is 5 hours but the offset for the 14th is 4 hours. And what about geographic areas that don't observe DST? Does a -7 hour offset represent Phoenix at any time in the year or Seattle during DST?
System functions for time zone awareness were not included in SQL 2008 along with datetimeoffset. It's an unfortunate exclusion considering that SQL Server has spatial data support and this is something that's built into .NET, Java, and most other platforms\languages. It would be incredibly useful to supply a datetime value, a spatial value (or a time zone name), and get a datetimeoffset with the correct offset in return.
I can write my own functions in TSQL to convert between time zones but I shouldn't have to. Here's why:
- As previously mentioned, this is something already available in other languages\platforms
- DST start\end dates are subject to change - and recently have in the United States. I'd rather get those updates via OS\product patches than have to update custom code.
- Time zone awareness is part of the SQL-92 standard.
Erland Sommarskog (Blog) submitted a Connect feature request for this (http://connect.microsoft.com/SQLServer/feedback/details/293933/add-a-set-timezone-command). Please consider voting for it to be included in a future version of SQL Server.
