question

Raj More avatar image
Raj More 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?
sql-server-2008-r2timezoneutc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
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
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
They can only send me time in CST or EST. I have to account for Timezone on my end.
0 Likes 0 ·
Is there a way to tell, based on who sends the data, or maybe how it is sent, which time zone the date represents?
0 Likes 0 ·
Yes, there is an accompanying column for Timezone.
0 Likes 0 ·
How do I account for Daylight savings?
0 Likes 0 ·
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.
0 Likes 0 ·
AaronBertrand avatar image
AaronBertrand answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.