Convert historic dates from utc to local time

datetime, gmt, sql, utc

Solution

This statement will work against the table you listed. Using the logic you can apply it to any date fields.

    SELECT CASE
         WHEN [theDate] BETWEEN 
         Convert(DATETIME, Convert(VARCHAR(4), Year([theDate])) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year([theDate])/4 + 4) % 7)) + ' 02:00:00', 20)
         AND
         Convert(DATETIME, Convert(VARCHAR(4), Year([theDate])) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year([theDate])/4 + 1) % 7)) + ' 03:00:00', 20)
         THEN Dateadd(hh, 2, [theDate])
         ELSE Dateadd(hh, 1, [theDate])
       END AS [theDate],
       [theValue]
FROM   [YOURTABLE]

Problem

I have an sql table with data like this: ``` | theDate (datetime) | theValue (int) | ---------------------------------------- | 2010-05-17 02:21:10 | 5 | | 2009-03-12 04:11:35 | 23 | | 2010-02-19 18:16:53 | 52 | | 2008-07-07 22:54:11 | 30 | ``` The dates are stored in UTC format in a datetime column, how can I convert them to local time (Norway)? Remember that the UTC-offset is not the same all year because of winter/summer-time. UPDATE: I am using Microsoft SQL Server 2005, and I need to do this from SQL because the data will be shown to a report later on.

Original source