将UTC日期时间转换为本地日期时间的最佳方法是什么？它不像getutcdate()和getdate()一样简单，因为差异取决于日期是什么。

CLR集成也不是我的选择。

我几个月前针对这个问题提出的解决方案是拥有一个日光节约时间表，该表存储接下来的100多年左右的开始和结束的日光节约时间，这种解决方案看起来不算什么，但是转换很快(简单表查询)

解决方案

回答

维护一个TimeZone表，或者使用扩展的存储过程(xp_cmdshell或者COM组件，或者我们自己的)外壳，并要求OS进行操作。如果我们使用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)

回答

如果这些问题之一影响我们，则永远不要在数据库中存储本地时间：

• DST的特点是在回退期周围存在"不确定的小时"，在该回退期中，本地时间无法明确转换。如果需要确切的日期和时间，则存储在UTC中。
• 如果要向用户显示他们自己的时区中的日期和时间，而不是操作发生的时区，请存储在UTC中。

回答

如果我们在美国，仅对从UTC / GMT到固定时区(例如EDT)感兴趣，则此代码就足够了。我今天整理了一下，并认为它是正确的，但使用风险自负。

假设日期在"日期"列上，则将计算列添加到表" myTable"中。希望其他人觉得这有用。

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)

回答

在Eric Z Beard的答案中，以下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的后端，这将导致2AM时间没有转换为1AM。当然，恰好是2AM的可能性很小，但是它可能发生，并且会导致无效的转换。