SQLServer-将日期字段转换为 UTC

小开

最佳答案

如果它们对你来说都是本地的，那么这就是补偿:

SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime

你应该能够使用以下方法更新所有数据:

UPDATE SomeTable
SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)

这样行得通吗，还是我漏掉了这个问题的另一个角度？

小开

I do not believe the above code will work. The reason is that it depends upon the difference between the current date in local and UTC times. For example, here in California we are now in PDT (Pacific Daylight Time); the difference between this time and UTC is 7 hours. The code provided will, if run now, add 7 hours to every date which is desired to be converted. But if a historical stored date, or a date in the future, is converted, and that date is not during daylight savings time, it will still add 7, when the correct offset is 8. Bottom line: you cannot convert date/times properly between time zones (including UTC, which does not obey daylight savings time) by only looking at the current date. You must consider the date itself that you are converting, as to whether daylight time was in force on that date. Furthermore, the dates at which daylight and standard times change themselves have changed (George Bush changed the dates during his administration for the USA!). In other words, any solution which even references getdate() or getutcdate() does not work. It must parse the actual date to be converted.

小开

下面的代码将计算所运行的服务器的 DATE 和 UTCDATE 之间的差异，并使用该偏移量计算传递给它的任何日期的 UTC 等价值。在我的示例中，我试图在澳大利亚阿德莱德转换“1-nov-201206:00”的 UTC 等效值，那里的 UTC 偏移量为 -630分钟，如果将其添加到任何日期，将导致 UTC 等效于任何本地日期。

选择 DATEADD (分钟，DATEDIFF (分钟，GETDATE () ，GETUTCDATE ()) ,’1-nov-201206:00’)

小开

如前所述，在 SQLServer 中没有执行时区规则感知日期转换的内置方法(至少在 SQLServer2012中是如此)。

要做好这件事，你基本上有三个选择:

在 SQLServer 外部执行转换并将结果存储在数据库中
在独立表中引入时区偏移量规则，并创建存储过程或 UDF 来引用规则表以执行转换。您可以找到一个采取这种方法在 SQL 服务器中心(需要注册)
您可以创建一个 SQLCLRUDF; 我将在这里描述这种方法

虽然 SQLServer 不提供执行时区规则感知日期转换的工具，但。NET 框架，只要您可以使用 SQLCLR，就可以利用这一点。

在 VisualStudio2012中，确保安装了数据工具(否则，SQLServer 项目将不会显示为选项) ，并创建一个新的 SQLServer 项目。

然后，添加一个新的 SQLCLR C # 用户定义函数，命名为“ ConvertToUtc”。VS 将为您生成如下图所示的锅炉板:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ConvertToUtc()
{
// Put your code here
return new SqlString (string.Empty);
}
}

我们想在这里做一些改变。首先，我们希望返回 SqlDateTime而不是 SqlString。其次，我们想做一些有用的事情。:)

修改后的代码应该是这样的:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToUtc(SqlDateTime sqlLocalDate)
{
// convert to UTC and use explicit conversion
// to return a SqlDateTime
return TimeZone.CurrentTimeZone.ToUniversalTime(sqlLocalDate.Value);
}
}

现在，我们已经准备好了。最简单的方法是在 VisualStudio 中使用内置的发布工具。右键单击数据库项目并选择“发布”。设置数据库连接和名称，然后单击“发布”将代码推入数据库，或者单击“生成脚本”(Generate Script) ，如果您想为后代存储脚本(或者将比特推入生产)。

一旦在数据库中有了 UDF，您就可以看到它的实际运作:

declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)

小开

根据需要回溯的时间，可以构建一个夏时制时间表，然后加入该表并进行 dst 敏感的转换。这个特殊的转换从 EST 到 GMT (即使用5和4的偏移量)。

select createdon, dateadd(hour, case when dstlow is null then 5 else 4 end, createdon) as gmt
from photos
left outer join (
SELECT {ts '2009-03-08 02:00:00'} as dstlow, {ts '2009-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2010-03-14 02:00:00'} as dstlow, {ts '2010-11-07 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2011-03-13 02:00:00'} as dstlow, {ts '2011-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2012-03-11 02:00:00'} as dstlow, {ts '2012-11-04 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2013-03-10 02:00:00'} as dstlow, {ts '2013-11-03 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2014-03-09 02:00:00'} as dstlow, {ts '2014-11-02 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2015-03-08 02:00:00'} as dstlow, {ts '2015-11-01 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2016-03-13 02:00:00'} as dstlow, {ts '2016-11-06 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2017-03-12 02:00:00'} as dstlow, {ts '2017-11-05 02:00:00'} as dsthigh
UNION ALL SELECT {ts '2018-03-11 02:00:00'} as dstlow, {ts '2018-11-04 02:00:00'} as dsthigh
) dst
on createdon >= dstlow and createdon < dsthigh

小开

如果你必须将今天以外的日期转换成不同的时区，那么你就必须处理夏令时的问题。我想要的解决方案不需要担心数据库版本，不需要使用存储函数，也不需要一些可以轻松移植到 Oracle 的东西。

我认为沃伦在获得正确的日光时间方面是正确的，但是为了让它对多个时区和不同国家的不同规则更有用，甚至是2006年至2007年间美国发生变化的规则，这里有一个上述解决方案的变体。注意，这里不仅有我们的时区，还有欧洲中部。中欧在四月的最后一个星期日和十月的最后一个星期日之后。你还会注意到，2006年的美国遵循的是四月的第一个星期天，十月的最后一个星期天。

这段 SQL 代码看起来可能有点难看，但是只要将它复制粘贴到 SQLServer 中并尝试使用它就可以了。请注意，有3个部分的年份，时区和规则。如果你还想要一年，只要把它加到年度工会里就行了。其他时区或规则也是如此。

select yr, zone, standard, daylight, rulename, strule, edrule, yrstart, yrend,
dateadd(day, (stdowref + stweekadd), stmonthref) dstlow,
dateadd(day, (eddowref + edweekadd), edmonthref)  dsthigh
from (
select yrs.yr, z.zone, z.standard, z.daylight, z.rulename, r.strule, r.edrule,
yrs.yr + '-01-01 00:00:00' yrstart,
yrs.yr + '-12-31 23:59:59' yrend,
yrs.yr + r.stdtpart + ' ' + r.cngtime stmonthref,
yrs.yr + r.eddtpart + ' ' + r.cngtime edmonthref,
case when r.strule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.stdtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.stdtpart) end
else (datepart(dw, yrs.yr + r.stdtpart) - 1) * -1 end stdowref,
case when r.edrule in ('1', '2', '3') then case when datepart(dw, yrs.yr + r.eddtpart) = '1' then 0 else 8 - datepart(dw, yrs.yr + r.eddtpart) end
else (datepart(dw, yrs.yr + r.eddtpart) - 1) * -1 end eddowref,
datename(dw, yrs.yr + r.stdtpart) stdow,
datename(dw, yrs.yr + r.eddtpart) eddow,
case when r.strule in ('1', '2', '3') then (7 * CAST(r.strule AS Integer)) - 7 else 0 end stweekadd,
case when r.edrule in ('1', '2', '3') then (7 * CAST(r.edrule AS Integer)) - 7 else 0 end edweekadd
from (
select '2005' yr union select '2006' yr -- old us rules
UNION select '2007' yr UNION select '2008' yr UNION select '2009' yr UNION select '2010' yr UNION select '2011' yr
UNION select '2012' yr UNION select '2013' yr UNION select '2014' yr UNION select '2015' yr UNION select '2016' yr
UNION select '2017' yr UNION select '2018' yr UNION select '2019' yr UNION select '2020' yr UNION select '2021' yr
UNION select '2022' yr UNION select '2023' yr UNION select '2024' yr UNION select '2025' yr UNION select '2026' yr
) yrs
cross join (
SELECT 'ET' zone, '-05:00' standard, '-04:00' daylight, 'US' rulename
UNION SELECT 'CT' zone, '-06:00' standard, '-05:00' daylight, 'US' rulename
UNION SELECT 'MT' zone, '-07:00' standard, '-06:00' daylight, 'US' rulename
UNION SELECT 'PT' zone, '-08:00' standard, '-07:00' daylight, 'US' rulename
UNION SELECT 'CET' zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename
) z
join (
SELECT 'US' rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
UNION SELECT 'US' rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
UNION SELECT  'EU' rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime
) r on r.rulename = z.rulename
and datepart(year, yrs.yr) between firstyr and lastyr
) dstdates

对于规则，使用1,2,3或 L 作为第一，第二，第三或最后一个星期天。对于规则类型 L，date 部分给出月份并根据规则、月的第一天或月的最后一天。

我将上述查询放入一个视图中。现在，无论何时我想要一个带有时区偏移或转换为 UTC 时间的日期，我只需加入到这个视图并选择以日期格式获取日期。我没有使用 datetime，而是将它们转换为 datetimeoffest。

select createdon, dst.zone
, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end pacificoffsettime
, TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end) pacifictime
, SWITCHOFFSET(TODATETIMEOFFSET(createdon, case when createdon >= dstlow and createdon < dsthigh then dst.daylight else dst.standard end), '+00:00')  utctime
from (select '2014-01-01 12:00:00' createdon union select '2014-06-01 12:00:00' createdon) photos
left join US_DAYLIGHT_DATES dst on createdon between yrstart and yrend and zone = 'PT'

小开

下面是一个测试过程，它将我的数据库从本地时间升级到 UTC 时间。升级数据库所需的唯一输入是，将本地时间从 utc 时间偏移到@Offset 的分钟数，如果时区通过设置@Apply yDaylightSavings 受夏令时调整的影响。

例如，美国中部时间将输入@Offset =-360和@Apply yDaylightSavings = 1，持续6小时，并且是应用夏时制调整。

支援资料库功能

CREATE FUNCTION [dbo].[GetUtcDateTime](@LocalDateTime DATETIME, @Offset smallint, @ApplyDaylightSavings bit)
RETURNS DATETIME AS BEGIN


--====================================================
--Calculate the Offset Datetime
--====================================================
DECLARE @UtcDateTime AS DATETIME
SET @UtcDateTime = DATEADD(MINUTE, @Offset * -1, @LocalDateTime)


IF @ApplyDaylightSavings = 0 RETURN @UtcDateTime;


--====================================================
--Calculate the DST Offset for the UDT Datetime
--====================================================
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME


--Get Year
SET @Year = YEAR(@LocalDateTime)


--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)




--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'


--Get DST EndDate
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)


--Finally add the DST Offset if needed
RETURN CASE WHEN @LocalDateTime BETWEEN @DSTStartDate AND @DSTEndDate THEN
DATEADD(MINUTE, -60, @UtcDateTime)
ELSE
@UtcDateTime
END


END
GO

升级脚本

在运行此脚本之前进行备份！
设置@Offset &@ApplicyDaylightSavings
只能跑一次！

begin try
begin transaction;


declare @sql nvarchar(max), @Offset smallint, @ApplyDaylightSavings bit;


set @Offset = -360;             --US Central Time, -300 for US Eastern Time, -480 for US West Coast
set @ApplyDaylightSavings = 1;  --1 for most US time zones except Arizona which doesn't observer daylight savings, 0 for most time zones outside the US


declare rs cursor for
select 'update [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '] set [' + a.COLUMN_NAME + '] = dbo.GetUtcDateTime([' + a.COLUMN_NAME + '], ' + cast(@Offset as nvarchar) + ', ' + cast(@ApplyDaylightSavings as nvarchar) + ') ;'
from INFORMATION_SCHEMA.COLUMNS a
inner join INFORMATION_SCHEMA.TABLES b on a.TABLE_SCHEMA = b.TABLE_SCHEMA and a.TABLE_NAME = b.TABLE_NAME
where a.DATA_TYPE = 'datetime' and b.TABLE_TYPE = 'BASE TABLE' ;


open rs;
fetch next from rs into @sql;
while @@FETCH_STATUS = 0 begin
exec sp_executesql @sql;
print @sql;
fetch next from rs into @sql;
end
close rs;
deallocate rs;


commit transaction;
end try
begin catch
close rs;
deallocate rs;


declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
rollback transaction;
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

小开

对于 SQLServer2016，现在内置了对带有 AT TIME ZONE语句的时区的支持。你可以链接这些进行转换:

SELECT YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC'

或者，这样也可以:

SELECT SWITCHOFFSET(YourOriginalDateTime AT TIME ZONE 'Pacific Standard Time', '+00:00')

其中任何一个都将解释太平洋时间的输入，正确说明是否有 DST 是有效的，然后转换为 UTC。结果将是一个零偏移的 datetimeoffset。

CTP 公告中的更多例子。

小开

除非我漏掉了上面的一些东西(可能) ，以上所有的方法都是有缺陷的，因为它们没有考虑到从夏令时(比如美国东部夏令时)到标准时(比如美国东部夏令时)的重叠。一个(非常冗长的)例子:

[1] EDT 2016-11-06 00:59 - UTC 2016-11-06 04:59
[2] EDT 2016-11-06 01:00 - UTC 2016-11-06 05:00
[3] EDT 2016-11-06 01:30 - UTC 2016-11-06 05:30
[4] EDT 2016-11-06 01:59 - UTC 2016-11-06 05:59
[5] EST 2016-11-06 01:00 - UTC 2016-11-06 06:00
[6] EST 2016-11-06 01:30 - UTC 2016-11-06 06:30
[7] EST 2016-11-06 01:59 - UTC 2016-11-06 06:59
[8] EST 2016-11-06 02:00 - UTC 2016-11-06 07:00

基于日期和时间的简单小时补偿不会减少它。如果你不知道当地时间是否记录在美国东部时间01:00和01:59之间，你将不会有一个线索！让我们用01:30来举个例子: 如果你发现在01:31到01:59之前的时间范围内，你不会知道你正在看的01:30是[3还是[6。在这种情况下，您可以通过一点编码来获得正确的 UTC 时间，查看以前的条目(在 SQL 中并不有趣) ，这是最好的情况..。

假设你有以下当地时间的记录，并且没有用一点东部夏季时间或者东部夏季时间来表示:

                     UTC time         UTC time         UTC time
if [2] and [3]   if [2] and [3]   if [2] before
local time           before switch    after switch     and [3] after
[1] 2016-11-06 00:43     04:43         04:43           04:43
[2] 2016-11-06 01:15     05:15         06:15           05:15
[3] 2016-11-06 01:45     05:45         06:45           06:45
[4] 2016-11-06 03:25     07:25         07:25           07:25

时间[2]和[3]可能在上午5点的时间范围内，6点的时间范围内，或者一个在上午5点，另一个在上午6点的时间范围内... ... 换句话说: 你被冲洗，必须扔掉01:00:00和01:59:59之间的所有读数。在这种情况下，绝对没有办法解决实际的 UTC 时间！

小开

这就是我的简单粗暴的版本。我知道我所有的约会对象都使用美国东部时区。您可以根据需要更改偏移量或使其更智能。我只做了一次迁移，所以这就足够了。

CREATE FUNCTION [dbo].[ConvertToUtc]
(
@date datetime
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @utcDate datetime;
DECLARE @offset int;


SET @offset = (SELECT CASE WHEN
@date BETWEEN '1987-04-05 02:00 AM' AND '1987-10-25 02:00 AM'
OR @date BETWEEN '1988-04-03 02:00 AM' AND '1988-10-30 02:00 AM'
OR @date BETWEEN '1989-04-02 02:00 AM' AND '1989-10-29 02:00 AM'
OR @date BETWEEN '1990-04-01 02:00 AM' AND '1990-10-28 02:00 AM'
OR @date BETWEEN '1991-04-07 02:00 AM' AND '1991-10-27 02:00 AM'
OR @date BETWEEN '1992-04-05 02:00 AM' AND '1992-10-25 02:00 AM'
OR @date BETWEEN '1993-04-04 02:00 AM' AND '1993-10-31 02:00 AM'
OR @date BETWEEN '1994-04-03 02:00 AM' AND '1994-10-30 02:00 AM'
OR @date BETWEEN '1995-04-02 02:00 AM' AND '1995-10-29 02:00 AM'
OR @date BETWEEN '1996-04-07 02:00 AM' AND '1996-10-27 02:00 AM'
OR @date BETWEEN '1997-04-06 02:00 AM' AND '1997-10-26 02:00 AM'
OR @date BETWEEN '1998-04-05 02:00 AM' AND '1998-10-25 02:00 AM'
OR @date BETWEEN '1999-04-04 02:00 AM' AND '1999-10-31 02:00 AM'
OR @date BETWEEN '2000-04-02 02:00 AM' AND '2000-10-29 02:00 AM'
OR @date BETWEEN '2001-04-01 02:00 AM' AND '2001-10-28 02:00 AM'
OR @date BETWEEN '2002-04-07 02:00 AM' AND '2002-10-27 02:00 AM'
OR @date BETWEEN '2003-04-06 02:00 AM' AND '2003-10-26 02:00 AM'
OR @date BETWEEN '2004-04-04 02:00 AM' AND '2004-10-31 02:00 AM'
OR @date BETWEEN '2005-04-03 02:00 AM' AND '2005-10-30 02:00 AM'
OR @date BETWEEN '2006-04-02 02:00 AM' AND '2006-10-29 02:00 AM'
OR @date BETWEEN '2007-03-11 02:00 AM' AND '2007-11-04 02:00 AM'
OR @date BETWEEN '2008-03-09 02:00 AM' AND '2008-11-02 02:00 AM'
OR @date BETWEEN '2009-03-08 02:00 AM' AND '2009-11-01 02:00 AM'
OR @date BETWEEN '2010-03-14 02:00 AM' AND '2010-11-07 02:00 AM'
OR @date BETWEEN '2011-03-13 02:00 AM' AND '2011-11-06 02:00 AM'
OR @date BETWEEN '2012-03-11 02:00 AM' AND '2012-11-04 02:00 AM'
OR @date BETWEEN '2013-03-10 02:00 AM' AND '2013-11-03 02:00 AM'
OR @date BETWEEN '2014-03-09 02:00 AM' AND '2014-11-02 02:00 AM'
OR @date BETWEEN '2015-03-08 02:00 AM' AND '2015-11-01 02:00 AM'
OR @date BETWEEN '2016-03-13 02:00 AM' AND '2016-11-06 02:00 AM'
OR @date BETWEEN '2017-03-12 02:00 AM' AND '2017-11-05 02:00 AM'
OR @date BETWEEN '2018-03-11 02:00 AM' AND '2018-11-04 02:00 AM'
OR @date BETWEEN '2019-03-10 02:00 AM' AND '2019-11-03 02:00 AM'
OR @date BETWEEN '2020-03-08 02:00 AM' AND '2020-11-01 02:00 AM'
OR @date BETWEEN '2021-03-14 02:00 AM' AND '2021-11-07 02:00 AM'
THEN 4
ELSE 5 END);


SELECT @utcDate = DATEADD(hh, @offset, @date)
RETURN @utcDate;


END

小开

我们可以将 ServerZone DateTime转换为 UTC，将 UTC 转换为 ServerZone DateTime

只需运行以下脚本来理解转换，然后根据需要进行修改

--Get Server's TimeZone
DECLARE @ServerTimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@ServerTimeZone OUT


-- ServerZone to UTC DATETIME
DECLARE @CurrentServerZoneDateTime DATETIME = GETDATE()
DECLARE @UTCDateTime  DATETIME =  @CurrentServerZoneDateTime AT TIME ZONE @ServerTimeZone AT TIME ZONE 'UTC'
--(OR)
--DECLARE @UTCDateTime  DATETIME = GETUTCDATE()
SELECT @CurrentServerZoneDateTime AS CURRENTZONEDATE,@UTCDateTime AS UTCDATE


-- UTC to ServerZone DATETIME
SET @CurrentServerZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @ServerTimeZone
SELECT @UTCDateTime AS UTCDATE,@CurrentServerZoneDateTime AS CURRENTZONEDATE

注意 : 这个(AT TIME ZONE) 仅在 SQLServer2016 + 上工作和这个优势是 自动考虑日光当转换到特定的时区

小开

我有点晚了，但是我需要在 SQL 2012上做一些类似的事情，我还没有完全测试它，但是这是我想出来的。

CREATE FUNCTION SMS.fnConvertUTC
(
@DateCST datetime
)
RETURNS DATETIME
AS
BEGIN
RETURN
CASE
WHEN @DateCST
BETWEEN
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-APR-07 02:00' ) + 1 END
AND
CASE WHEN @DateCST > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(@DateCST)) + '-OCT-31 02:00' ) + 1 END
THEN DATEADD(HOUR,4,@DateCST)
ELSE DATEADD(HOUR,5,@DateCST)
END
END

上面有人张贴了一个静态列表 DST 日期，所以我写了下面的查询，以比较这个代码的输出到该列表... 到目前为止，它看起来是正确的。

;WITH DT AS
(
SELECT MyDate = GETDATE()
UNION ALL
SELECT MyDate = DATEADD(YEAR,-1,MyDate) FROM DT
WHERE DATEADD(YEAR,-1,MyDate) > DATEADD(YEAR, -30, GETDATE())
)
SELECT
SpringForward = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-MAR-14 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-APR-07 02:00' ) + 1 END
,   FallBackward  = CASE
WHEN MyDate > '2007-01-01'
THEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-NOV-07 02:00' ) + 1
ELSE CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00') - DATEPART(DW,CONVERT(VARCHAR,YEAR(MyDate)) + '-OCT-31 02:00' ) + 1 END
FROM DT
ORDER BY 1 DESC

SpringForward      FallBackward
----------------   ----------------
2020-03-08 02:00   2020-11-01 02:00
2019-03-10 02:00   2019-11-03 02:00
2018-03-11 02:00   2018-11-04 02:00
2017-03-12 02:00   2017-11-05 02:00
2016-03-13 02:00   2016-11-06 02:00
2015-03-08 02:00   2015-11-01 02:00
2014-03-09 02:00   2014-11-02 02:00
2013-03-10 02:00   2013-11-03 02:00
2012-03-11 02:00   2012-11-04 02:00
2011-03-13 02:00   2011-11-06 02:00
2010-03-14 02:00   2010-11-07 02:00
2009-03-08 02:00   2009-11-01 02:00
2008-03-09 02:00   2008-11-02 02:00
2007-03-11 02:00   2007-11-04 02:00
2006-04-02 02:00   2006-10-29 02:00
2005-04-03 02:00   2005-10-30 02:00
2004-04-04 02:00   2004-10-31 02:00
2003-04-06 02:00   2003-10-26 02:00
2002-04-07 02:00   2002-10-27 02:00
2001-04-01 02:00   2001-10-28 02:00
2000-04-02 02:00   2000-10-29 02:00
1999-04-04 02:00   1999-10-31 02:00
1998-04-05 02:00   1998-10-25 02:00
1997-04-06 02:00   1997-10-26 02:00
1996-04-07 02:00   1996-10-27 02:00
1995-04-02 02:00   1995-10-29 02:00
1994-04-03 02:00   1994-10-30 02:00
1993-04-04 02:00   1993-10-31 02:00
1992-04-05 02:00   1992-10-25 02:00
1991-04-07 02:00   1991-10-27 02:00


(30 row(s) affected)