我最近更新了我的系统，将日期/时间记录为UTC，因为之前它们是以本地时间存储的。
现在我需要将所有本地存储的日期/时间转换为UTC。我想知道是否有内置函数类似于.NET的ConvertTime方法？
我想避免编写一个实用程序来帮助我完成这个工作。
有什么建议吗？
SQL Server - 将日期字段转换为UTC
79
- James
3
1这些时间对你来说是“本地时间”，还是它们来自多个时区的不同时间？ - SqlRyan
@rwmnau，他们对我来说都是本地的。 - James
可能是重复的问题：TSQL：如何将本地时间转换为UTC？（SQL Server 2008） - Reinstate Monica Please
12个回答
91
我不认为上面的代码会起作用，因为它依赖于本地时间和UTC时间之间的差异。例如，在加州我们现在处于太平洋夏令时（PDT）; 这个时间和UTC之间的差距是7个小时。如果运行提供的代码，它将为每个所需转换的日期添加7个小时。但是，如果转换一个历史存储的日期或未来日期，并且该日期不在夏令时期间，则仍将添加7，而正确的偏移量为8。底线：您不能仅仅通过查看当前日期就正确地在时区之间（包括不遵守夏令时的UTC）转换日期/时间。您必须考虑正在转换的日期本身，以确定当时是否使用了夏令时。此外，夏令时和标准时间更改的日期本身也已经发生改变（乔治·布什在他的总统任期内为美国更改了这些日期！）。换句话说，任何甚至参考了getdate() 或 getutcdate()的解决方案都无法正常工作。它必须解析要转换的实际日期。
- Roderick Llewellyn
2
2你可以创建一个CLR函数，使用C#来转换时区并考虑夏令时的影响。http://www.jitbit.com/maxblog/17-sql-server-how-to-convert-datetime-to-utc/ - Alex from Jitbit
这里所述的每件事情都是正确的。我在此之后五年发布的新帖子，在本地 SQL Server 用户定义函数中考虑了所有这些问题。 - Ben Gripka
80
使用 SQL Server 2016，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。
- Matt Johnson-Pint
5
2为什么首先是太平洋标准时间？ - xr280xr
@xr280xr - 这个程序将特定时区的时间转换为UTC时间。这里只是举了太平洋时区作为例子。 - Matt Johnson-Pint
此解决方案仅适用于SQL Server 2016及以上版本。 - Aakash Uniyal
4该方法有一个小缺陷需要注意。无法确定夏令时结束的小时与UTC之间的正确偏移量。考虑到“2018-11-04 01:00:00”是从中央标准时间写入的日期时间。这个01:00:00小时实际上可以表示UTC的-05:00或-06:00偏移量，具体取决于它是在夏令时结束前还是结束后被写入。如果没有附带的偏移量信息，则无法确定。Microsoft SQL Server的行为是使用-05:00偏移量，这可能是正确的，也可能不正确。 - Corbin
1@Corbin - 确实，这是一个限制。我使用我的 SQL Server 时区支持 项目明确解决了这个问题（请参见
LocalToUtc 和 ConvertZone 的两个尾随参数），但内置的 AT TIME ZONE 不允许控制这个方面。 - Matt Johnson-Pint38
如果它们都在您的本地，那么这就是偏移量：
SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime
你应该能够使用以下代码更新所有数据：
UPDATE SomeTable
SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)
这样行得通吗，还是我忽略了这个问题的其他方面？
- SqlRyan
8
看起来不错，我会试一下。 - James
我遇到了“dateadd函数的第2个参数的参数数据类型无效，应该是datetime类型”的错误。有什么想法吗？ - James
2@James：你需要在调用中颠倒第二个和第三个参数：
DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp) -- 第二个参数是时间间隔（要添加或减去的小时数），第三个参数是应用该时间间隔的值。 - marc_s63不！区别取决于确切的日期。这取决于夏令时。 - usr
16这并没有考虑到夏令时。请参考Roderick Llewellyn下面的答案。 - Tom Hamming
显示剩余3条评论
16
如此前所述，SQL Server（至少在 SQL Server 2012）没有内置的方法来执行时区规则感知的日期转换。
您基本上有三个选择来正确地执行此操作：
- 在 SQL Server 外部执行转换并将结果存储在数据库中
- 引入独立表中的时区偏移规则，并创建存储过程或 UDF 引用规则表来执行转换。您可以在 SQL Server Central 找到对此方法的看法（需要注册）
- 您可以创建一个 SQL CLR UDF；我将在此处描述该方法
虽然 SQL Server 没有提供执行时区规则感知日期转换的工具，但 .NET Framework 提供了支持，只要您能使用 SQL CLR，就可以利用它。
在 Visual Studio 2012 中，请确保安装了数据工具（否则，SQL Server 项目不会显示为选项），并创建一个新的 SQL Server 项目。
然后，添加一个新的 SQL CLR 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);
}
}
现在，我们已经准备好尝试它了。最简单的方法是使用Visual Studio中内置的发布工具。右键单击数据库项目，选择“发布”。设置您的数据库连接和名称，然后点击“发布”将代码推送到数据库，或者点击“生成脚本”以保存脚本供后人参考（或将位推送到生产环境）。
一旦您在数据库中拥有UDF，就可以看到它的作用：
declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)
- Michael Teper
3
我认为这不适用于所有历史日期。“ToUniversalTime”方法仅识别本地时区的当前夏令时调整规则。因此，在最新的调整规则生效期间，它保证准确返回与特定本地时间对应的协调世界时（UTC）。如果时间是一个历史日期和时间值，曾经受到先前调整规则的影响，则可能返回不准确的结果。 - oatsoda
1你可能需要使用TimeZone.IsDaylightSavingTime并自行处理转换。"假设操作系统本身具有准确的历史夏令时数据，使用TimeZoneInfo.IsDaylightSavingTime方法可以获得更准确的结果。尽可能使用TimeZoneInfo.IsDaylightSavingTime方法。" - oatsoda
在.NET 4.0之后，您可以使用
sqlLocalValue.Value.ToUniversalTime()并获得正确的结果。请参见https://dev59.com/Z0rSa4cB1Zd3GeqPUjkt#31234152。 - Dan Friedman13
这里是一个经过测试的程序，可以将我的数据库从本地时间升级到UTC时间。升级数据库所需的唯一输入是将本地时间与UTC时间之间的分钟数输入@Offset中，并通过设置@ApplyDaylightSavings来确定时区是否受夏令时调整影响。
例如，美国中部时间应该将@Offset设置为-360，将@ApplyDaylightSavings设置为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和@ApplyDaylightSavings
- 仅运行一次！
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
- Ben Gripka
5
如果你需要将日期从今天以外的时间转换为不同的时区，那么你需要考虑夏令时。我想要一个解决方案，能够在不用担心数据库版本和不使用存储函数的情况下进行，并且可以轻松地移植到Oracle上。
我认为Warren在获取夏令时正确日期方面已经有了正确的方法，但是为了使其在多个时区和不同国家甚至是美国2006年至2007年之间发生变化的规则中更加有用，这里提供了以上解决方案的变体。请注意，它不仅适用于美国的时区，还适用于中欧地区。中欧遵循4月的最后一个星期日和10月的最后一个星期日。您还会注意到，美国在2006年遵循旧的4月第一个星期日，10月最后一个星期日的规则。
这段SQL代码可能看起来有点丑陋，但只需将其复制并粘贴到SQL Server中即可尝试。请注意，共有三个部分：年份、时区和规则。如果您需要另一年，请将其添加到年份union中。同样，如果需要其他时区或规则，请添加相应的部分。
我认为Warren在获取夏令时正确日期方面已经有了正确的方法，但是为了使其在多个时区和不同国家甚至是美国2006年至2007年之间发生变化的规则中更加有用，这里提供了以上解决方案的变体。请注意，它不仅适用于美国的时区，还适用于中欧地区。中欧遵循4月的最后一个星期日和10月的最后一个星期日。您还会注意到，美国在2006年遵循旧的4月第一个星期日，10月最后一个星期日的规则。
这段SQL代码可能看起来有点丑陋，但只需将其复制并粘贴到SQL Server中即可尝试。请注意，共有三个部分：年份、时区和规则。如果您需要另一年，请将其添加到年份union中。同样，如果需要其他时区或规则，请添加相应的部分。
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表示第一、第二、第三或最后一个星期日。日期部分给出了月份，根据规则类型，是月初还是月末的最后一天。
我将上面的查询放入了一个视图中。现在，每当我需要带有时区偏移量或转换为UTC时间的日期时，我只需加入这个视图并选择以日期格式获取日期。我将这些从datetime转换为datetimeoffset。
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'
- Chris Barlow
4
这是否会涵盖过去的日期/时间字段？还是只适用于当前的规则？ - Joshua F. Rountree
请原谅我，我现在明白你在做什么。 - Joshua F. Rountree
你有没有第二个查询的示例，不使用2008+的东西。我的数据库是2005。有什么建议吗？ - Joshua F. Rountree
@JoshuaRountree，第一个视图是最重要的部分，我认为它没有使用2005年没有的任何东西。我猜偏移量可能是你的问题。尝试删除那些列。第一个视图可能是你需要的，只要你首先查看执行转换的方式就可以了。 - Chris Barlow
3
以下是我的简单粗暴版本。我知道所有的日期都使用美国东部时区。您可以根据需要更改偏移量或使其更智能化。我只进行了一次迁移，所以这已经足够好。
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
- AndyMcKenna
1
除非我在上面漏了什么（可能性很大），上述所有方法的缺陷在于它们没有考虑从夏令时（例如EDT）切换到标准时间（例如EST）时的重叠。以下是一个（非常冗长的）例子：
[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之间是以EDT还是EST记录的，那么你就一无所知了！让我们以01:30为例：如果你在它之前的范围内找到了01:31到01:59的较晚时间，那么你就不知道你正在查看的01:30是[3还是[6。在这种情况下，你可以通过编写一些代码来查看先前的条目（在SQL中不好玩）来获取正确的UTC时间，这是最好的情况......
假设你记录了以下本地时间，并且没有专门指示EDT或EST：
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
第二个和第三个时间可能在早上5点的时间段，早上6点的时间段，或者一个在早上5点，另一个在早上6点的时间段……换句话说：你完了，必须放弃01:00:00到01:59:59之间的所有读数。在这种情况下，绝对没有办法确定实际的UTC时间！
- Gil
0
我们可以将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) 仅适用于SQL Server 2016及以上版本，并且在转换到特定时区时会自动考虑夏令时
- KarthikeyanMlp
0
我有点晚了，但我需要在 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)
- Chris Rodriguez
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接