asked
Timezone shifts using TSQL
I have incoming hourly data (5 years worth of data) with timestamps recorded in CST and EST. I want to store this in my database in UTC time. Is there a way for me to convert from CST/CDT/CPT or EST/EDT/EPT to UTC using TSQL?
You can use datetimeoffset (more at [this Stack Overflow thread][1]) to do the conversion, though you'll need to know coming in which time zone your date represents. Unfortunately, the easiest way to handle a requirement like this is to have the source system send UTC dates. You can use GETUTCDATE() in SQL Server to get the current UTC date, but if the source system can't (or won't) change, it's more complicated to fix on your end. [1]:
http://stackoverflow.com/questions/1205142/tsql-how-to-convert-local-time-to-utc-sqlserver2008
Okay. In that case, you would be able to use datetimeoffset, probably in conjunction with a case statement, to get the UTC date based on the one you receive.
Is there a way to tell, based on who sends the data, or maybe how it is sent, which time zone the date represents?
Unfortunately, in this case, you would have to track that yourself. Erland Sommarskog has a Connect item which would let you track time zones (
http://connect.microsoft.com/SQLServer/feedback/details/293933/add-a-set-timezone-command), but it didn't make it into SQL Server 2008. The datetimeoffset is not Daylight Savings Time aware. It might be a good idea to populate a utility table with daylight savings time beginning and end dates and times, and that could tell you whether your date/time combination is in *ST or *DT. It's something that really should have been in SQL Server, I think, but it looks like it's not, unfortunately.
One way to account for daylight savings would be to use a calendar table. In this example I use hours but you are much better off using SMALLINT and minutes, since not all timezones are hourly (Newfoundland, for example).
http://www.aspfaq.com/2519
Good point Aaron, I would also add, be careful if you are getting your incoming data from an XML Web Service that crosses time zones, sometimes it likes to "help" you by adjusting the time. If this is a problem you can decorate the data member with the appropriate type for the SOAP envelope.
