Convert historic dates from utc to local time

Question

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.

Answer 1

Using PHP if you can...

$dateFormat = 'r';
$tz         = 'Australia/Perth';
function convertDate($epochDate) {
  global $dateFormat, $tz;
  $tzOld = getenv("TZ");
  putenv("TZ=$tz");
  $ret = date($dateFormat, ($epochDate*1));
  putenv("TZ" . ($tzOld ? "=$tzOld" : ""));
  return $ret;
}

Answer 2

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]