Поддерживайте таблицу TimeZone или выполняйте оболочку с помощью расширенного хранимого процесса (xp_cmdshell, COM-компонент или ваш собственный) и попросите ОС сделать это. Если вы выберете маршрут xp, вы, вероятно, захотите кэшировать смещение на день.

Создайте две таблицы, а затем присоединитесь к ним, чтобы преобразовать сохраненные даты GMT в местное время:

TimeZones     e.g.
---------     ----
TimeZoneId    19
Name          Eastern (GMT -5)
Offset        -5

Создайте таблицу летнего времени и заполните ее как можно большим объемом информации (местные законы постоянно меняются, поэтому невозможно предсказать, как будут выглядеть данные через годы в будущем)

DaylightSavings
---------------
TimeZoneId    19
BeginDst      3/9/2008 2:00 AM
EndDst        11/2/2008 2:00 AM

Присоединяйтесь к ним так:

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone 
    and x.TheDateToConvert between ds.BeginDst and ds.EndDst

Преобразуйте даты так:

dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, TheDateToConvert)

Если любая из этих проблем влияет на вас, никогда не следует сохранять местное время в базе данных:

1. При переходе на летнее время существует «час неопределенности» относительно периода спада, когда местное время не может быть однозначно преобразовано. Если требуются точные даты и время, сохраните их в формате UTC.
2. Если вы хотите показывать пользователям дату и время в их собственном часовом поясе, а не в часовом поясе, в котором произошло действие, сохраните в формате UTC.

Если вы находитесь в США и заинтересованы только в переходе от 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)

В Ответ Эрика Зи Берда следующий SQL

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId 
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone  
    and x.TheDateToConvert between ds.BeginDst and ds.EndDst 

может быть более точно:

inner join  TimeZones       tz on x.TimeZoneId=tz.TimeZoneId 
left join   DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone  
    and x.TheDateToConvert >= ds.BeginDst and x.TheDateToConvert < ds.EndDst 

(код выше не протестирован)

Причина этого в том, что в sql "between" указано включение. На стороне DST это приведет к тому, что время 2:00 НЕ будет преобразовано в 1:00. Конечно, вероятность того, что время будет ровно 2 часа ночи, мала, но это может произойти, и это приведет к недействительному преобразованию.

ТОЛЬКО ДЛЯ ЧТЕНИЯ Используйте это (вдохновлено Неправильное решение Боба Олбрайта):

SELECT
  date1, 
  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 (which, for edt, is 07:00 UTC at the start, and 06:00 GMT at the end).
    CASE WHEN YEAR(date1) <= 2006 THEN
         CASE WHEN 
                  date1 >=  '4/' + CAST((8-DATEPART(dw,'4/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date1) as varchar) + ' 7:00' 
                AND 
                  date1 < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date1) as varchar)) as varchar) +  '/' + CAST(YEAR(date1) as varchar) + ' 6: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. In 2008 
        -- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8
        CASE WHEN 
                 date1 >= '3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(date1) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(date1) as varchar) + ' 7:00' 
               AND 
                 date1 < '11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(date1) as varchar) + ' 6:00' 
             THEN -4 ELSE -5 END
    END
   , date1) as date1Edt
  from MyTbl

Я опубликовал этот ответ после того, как попытался отредактировать Неправильный ответ Боба Олбрайта. Я исправил время и удалил лишний пресс (), но мои правки несколько раз отклонялись. Я пытался объяснить, но меня отвергли как новичка. У него ОТЛИЧНЫЙ подход к проблеме! Это заставило меня двигаться в правильном направлении. Мне не нравится создавать этот отдельный ответ, когда ему просто нужна небольшая настройка, но я попробовал ¯ \ _ (ツ) _ / ¯

Гораздо более простое и универсальное решение, учитывающее летнее время. Учитывая дату в формате UTC в "YourDateHere":

--Use Minutes ("MI") here instead of hours because sometimes
--  the UTC offset may be half an hour (e.g. 9.5 hours).
SELECT DATEADD(MI,
               DATEDIFF(MI, SYSUTCDATETIME(),SYSDATETIME()),
               YourUtcDateHere)[LocalDateTime]

Мне нравится ответ, предоставленный @Eric Z Beard.

Однако что насчет этого, чтобы избежать выполнения соединения каждый раз?

TimeZoneOffsets
---------------
TimeZoneId    19
Begin         1/4/2008 2:00 AM
End           1/9/2008 2:00 AM
Offset        -5
TimeZoneId    19
Begin         1/9/2008 2:00 AM
End           1/4/2009 2:00 AM
Offset        -6
TimeZoneId    20 --Hong Kong for example - no DST
Begin         1/1/1900
End           31/12/9999
Offset        +8

потом

 Declare @offset INT = (Select IsNull(tz.Offset,0) from YourTable ds
 join   TimeZoneOffsets tz on tz.TimeZoneId=ds.LocalTimeZoneId  
 and x.TheDateToConvert >= ds.Begin and x.TheDateToConvert < ds.End)

наконец становится

 dateadd(hh, @offset, TheDateToConvert)

Я прочитал много сообщений StackOverflow по этой проблеме и нашел много методов. Какое-то «вроде» нормально. Я также нашел эту ссылку на MS (https://msdn.microsoft.com/en-us/library/mt612795.aspx), которую я попытался использовать в своем скрипте. Мне удалось добиться требуемого результата, НО я не уверен, будет ли это работать на версии 2005 года. В любом случае, я надеюсь, что это поможет.

Fnc для возврата PST из системного UTC по умолчанию

CREATE FUNCTION dbo.GetPst()
RETURNS DATETIME
AS 
BEGIN

    RETURN  SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time'

END

SELECT dbo.GetPst()

Fnc для возврата PST из предоставленной метки времени

CREATE FUNCTION dbo.ConvertUtcToPst(@utcTime DATETIME)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(HOUR, 0 - DATEDIFF(HOUR, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS DATETIME), SYSDATETIME()), @utcTime)

END


SELECT dbo.ConvertUtcToPst('2016-04-25 22:50:01.900')

Я использую это, потому что все мои свидания с этого момента.

DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, GETDATE())

Для исторических дат (или для обработки будущих изменений в DST, я предполагаю, что решение Боба Олбрайта будет подходящим вариантом.

Модификация, которую я вношу в свой код, заключается в использовании целевого столбца:

DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, [MySourceColumn])

Пока что вроде работает, но я рад получить обратную связь.

Вот код, который я использую для создания таблицы часовых поясов. Это немного наивно, но обычно достаточно хорошо.

Предположения:

1. Он предполагает правила только для США (летнее время - 2 часа ночи в какое-то заранее определенное воскресенье, так далее).
2. Предполагается, что у вас нет дат до 1970 года.
3. Предполагается, что вы знаете смещение местного часового пояса (например: EST = -05: 00, EDT = -04: 00 и т. д.)

Вот SQL:

-- make a table (#dst) of years 1970-2101. Note that DST could change in the future and
-- everything was all custom and jacked before 1970 in the US.
declare @first_year varchar(4) = '1970'
declare @last_year varchar(4) = '2101'

-- make a table of all the years desired
if object_id('tempdb..#years') is not null drop table #years
;with cte as (
    select cast(@first_year as int) as int_year
          ,@first_year as str_year
          ,cast(@first_year + '-01-01' as datetime) as start_of_year
    union all
    select int_year + 1
          ,cast(int_year + 1 as varchar(4))
          ,dateadd(year, 1, start_of_year)
    from cte
    where int_year + 1 <= @last_year
)
select *
into #years
from cte
option (maxrecursion 500);

-- make a staging table of all the important DST dates each year
if object_id('tempdb..#dst_stage') is not null drop table #dst_stage
select dst_date
      ,time_period
      ,int_year
      ,row_number() over (order by dst_date) as ordinal
into #dst_stage
from (
    -- start of year
    select y.start_of_year as dst_date
          ,'start of year' as time_period
          ,int_year
    from #years y

    union all
    select dateadd(year, 1, y.start_of_year)
          ,'start of year' as time_period
          ,int_year
    from #years y
    where y.str_year = @last_year

    -- start of dst
    union all
    select
        case
            when y.int_year >= 2007 then
                -- second sunday in march
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-03-08')) + 1) % 7, y.str_year + '-03-08')
            when y.int_year between 1987 and 2006 then
                -- first sunday in april
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-01')) + 1) % 7, y.str_year + '-04-01')
            when y.int_year = 1974 then
                -- special case
                cast('1974-01-06' as datetime)
            when y.int_year = 1975 then
                -- special case
                cast('1975-02-23' as datetime)
            else
                -- last sunday in april
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-24')) + 1) % 7, y.str_year + '-04-24')
        end
        ,'start of dst' as time_period
        ,int_year
    from #years y

    -- end of dst
    union all
    select
        case
            when y.int_year >= 2007 then
                -- first sunday in november
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-11-01')) + 1) % 7, y.str_year + '-11-01')
            else
                -- last sunday in october
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-10-25')) + 1) % 7, y.str_year + '-10-25')
        end
        ,'end of dst' as time_period
        ,int_year
    from #years y
) y
order by 1

-- assemble a final table
if object_id('tempdb..#dst') is not null drop table #dst
select a.dst_date +
          case
             when a.time_period = 'start of dst' then ' 03:00'
             when a.time_period = 'end of dst' then ' 02:00'
             else ' 00:00'
          end as start_date
      ,b.dst_date +
          case
             when b.time_period = 'start of dst' then ' 02:00'
             when b.time_period = 'end of dst' then ' 01:00'
             else ' 00:00'
          end as end_date
      ,cast(case when a.time_period = 'start of dst' then 1 else 0 end as bit) as is_dst
      ,cast(0 as bit) as is_ambiguous
      ,cast(0 as bit) as is_invalid
into #dst
from #dst_stage a
join #dst_stage b on a.ordinal + 1 = b.ordinal
union all
select a.dst_date + ' 02:00' as start_date
      ,a.dst_date + ' 03:00' as end_date
      ,cast(1 as bit) as is_dst
      ,cast(0 as bit) as is_ambiguous
      ,cast(1 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'start of dst'
union all
select a.dst_date + ' 01:00' as start_date
      ,a.dst_date + ' 02:00' as end_date
      ,cast(0 as bit) as is_dst
      ,cast(1 as bit) as is_ambiguous
      ,cast(0 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'end of dst'
order by 1

-------------------------------------------------------------------------------

-- Test Eastern
select
    the_date as eastern_local
    ,todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end) as eastern_local_tz
    ,switchoffset(todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end), '+00:00') as utc_tz
    --,b.*
from (
    select cast('2015-03-08' as datetime) as the_date
    union all select cast('2015-03-08 02:30' as datetime) as the_date
    union all select cast('2015-03-08 13:00' as datetime) as the_date
    union all select cast('2015-11-01 01:30' as datetime) as the_date
    union all select cast('2015-11-01 03:00' as datetime) as the_date
) a left join
#dst b on b.start_date <= a.the_date and a.the_date < b.end_date

--Adapted Bob Albright and WillDeStijl suggestions for SQL server 2014
--
--In this instance I had no dates prior to 2006, therefore I simplified the case example
--I had to add the variables for the assignment to allow trimming the timestamp from my resultset 

DECLARE @MARCH_DST as DATETIME
SET @MARCH_DST='3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 8 as varchar) +  '/' + CAST(YEAR(getdate()) as varchar) + ' 7:00'

DECLARE @NOV_DST as DATETIME
SET @NOV_DST='11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 1 as varchar) +  '/' + CAST(YEAR(getdate()) as varchar) + ' 6:00'

select cast(dateadd(HOUR,
-- 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. In 2008 
        -- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8
       CASE WHEN
                date1 >=@MARCH_DST
            AND
                date1< @NOV_DST
       THEN -4 ELSE -5 END
       , date1) as DATE) as date1_edited