2

My database have date values saved in GMT time zone in int format. I am trying to convert the date to local timezone, but the issue arises when the date is a past date, For instance in my case offset for date Dec 1, 2012 will be -5 and for June 15, 2010 will be -4 due to daylight savings. I am currently in EST.

So i need to know what was the UTC date of a previous date to be able to determine whether -4 or -5 will be the offset for that date.

SELECT Test_Number,
Last_Test_Date, dateAdd(hour, 
        datediff(hour, GETUTCDATE(), getdate()), --UTC offset
        dateadd(second, Last_Test_Date, '1/1/1970 12:00 AM'))
FROM TestTable

I am not entirely sure if it is even possible. Any opinion ?

Nanu
  • 3,010
  • 10
  • 38
  • 52
  • 2
    Use a calendar table. You can't use today's offset to calculate an offset. in the past. Especially since the dates are different every year, and the actual rules change, too. The offsets at the end of DST 10 years ago are different from last year, for example. – Aaron Bertrand Nov 14 '13 at 21:42
  • Don't depend on Windows runtime code if you are serious about this. Older version only supported a single set of UTC timezone per timezone, I think that it still only supports 2 ways in the latest versions. – Gary Walker Nov 14 '13 at 21:47

1 Answers1

1

It sounds like you are actually looking to convert a UTC time stored in the database to a local date/time with the correct offset (-5 or -4).

There is no good way to do this in SQL Server. At least, not yet.

The best advice I can offer is to not do this in conversion in the database. Instead, pass the UTC value as-is back to your application code, and do the conversion there.

For example, if your application is written in .NET, you can use TimeZoneInfo or Noda Time to handle the conversions to any time zone you wish. If you're using something else, let me know and I will update the question.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thanks Matt, I am using SSRS, not sure if we can code anything in there to dynamically handle dates and UTC time. – Nanu Nov 18 '13 at 16:44