Эффективно Преобразовывающие даты между UTC и Локальный (т.е. PST) время в SQL 2005

Если вы находитесь в США и заинтересованы только в переходе с UTC / GMT на фиксированный часовой пояс (например, EDT), этого кода должно быть достаточно. Я сделал это сегодня и считаю, что это правильно, но используйте на свой страх и риск.

Добавляет вычисляемый столбец в таблицу myTable, предполагая, что ваши даты находятся в столбце «date». Надеюсь, кто-то еще сочтет это полезным.

ALTER TABLE myTable ADD date_edt AS 
  dateadd(hh, 
        -- The schedule through 2006 in the United States was that DST began on the first Sunday in April 
        -- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006). 
        -- The time is adjusted at 02:00 local time.
              CASE WHEN YEAR(date) <= 2006 THEN  
                    CASE WHEN 
                              date >=  '4/' + CAST(abs(8-DATEPART(dw,'4/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                          AND 
                              date < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date) as varchar)) as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                    THEN -4 ELSE -5 END
              ELSE
        -- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007. 
        -- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on 
        -- the first Sunday of November, one week later than in years past. This change resulted in a new DST period 
        -- that is four weeks (five in years when March has five Sundays) longer than in previous years.[35] In 2008 
        -- daylight saving time ended at 02:00 on Sunday, November 2, and in 2009 it began at 02:00 on Sunday, March 8.[36]
                    CASE WHEN 
                              date >= '3/' + CAST(abs(8-DATEPART(dw,'3/1/' + CAST(YEAR(date) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                          AND 
                              date < 
                                '11/' + CAST(abs(8-DATEPART(dw,'11/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date) as varchar) + ' 2:00' 
                    THEN -4 ELSE -5 END
              END
  ,date)