I have seen many comments on how to handle time conversion in SQL and lots of them seem to involve the maintenance of tables to handle the time shift that needs to occur. Lots of these examples only shift time by time zones which is not always an accurate way to shift time. Let’s look at the following example:
- 3/13/2011 3:30 AM EST – Lets assume that in your organization the center of the universe is in Central Standard Time. If we were to just shift the date by one hour to CST we would end up with a date timestamp of 3/13/2011 2:30 AM EST. One problem that date timestamp doesn’t exist since it falls into the Daylight Savings Time Black Hole.
So how do we do this without things getting overly complicated. Well fortunately there is such a feature built into Windows all we need to do is tap into it.
All this takes is three lines of code in a Script Component Task.
TimeZoneInfo ESTtz = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
TimeZoneInfo CSTtz = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");
Row.PostTimeCST = TimeZoneInfo.ConvertTime(Row.PostTime, ESTtz, CSTtz);
We need to obtain the Source and Destination time zones. Then we use the ConvertTime method to convert the row’s date from one time zone to another.
To find a complete list of Time Zones on your machine look in the following registry location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
References:
Ben Holcombe 