我想在T-SQL中循环一段时间，并打印UTC日期时间和我们本地的时间变体。我们生活在UTC +1，因此我可以轻松地添加1小时，但在夏令时期间我们生活在UTC +2。
在C#中，我可以创建一个DateTime并使用方法来获取UTC变体，反之亦然。
到目前为止，我的代码是这样的：
declare @counter int
declare @localdate datetime
declare @utcdate datetime
set @counter = 0
while @counter < 100
begin
set @counter = @counter + 1
print 'The counter is ' + cast(@counter as char)
set @utcdate = DATEADD(day,@counter,GETUTCDATE())
--set @localdate = ????
print @localdate
print @utcdate
end
我已经等待了5年，希望能有更加优雅的解决方案，但由于并没有出现，所以我将发布到目前为止一直在使用的方法...
CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)
RETURNS DATETIME
AS
BEGIN
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5
--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)
--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDate)
--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)
--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END
--====================================================
--Finally add the DST Offset
--====================================================
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
END
GO
注：
该代码适用于遵守夏令时的北美服务器。请将变量@Offest更改为运行SQL函数的服务器的时区偏移量（在不观察夏令时的情况下）...
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5
当夏令时规则发生变化时，请在此更新规则...
--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)
祝好！
[SqlFunction()]
public static SqlDateTime ConvertUtcToLocal(SqlDateTime utcDate)
{
// over to you to convert SqlDateTime to DateTime, specify Kind
// as UTC, convert to local time, and convert back to SqlDateTime
}
set @localdate = dbo.ConvertUtcToLocal(@utcdate)
SQL CLR有部署方面的开销，但我觉得像这样的情况是它最适合的。
TimeZoneInfo 在 System.Core 中是其中之一，从 SQL Server 2008 开始提供。因此，在 SQL Server 2005 中将不可用。另一方面，使用 TimeZoneInfo 需要将程序集设置为 UNSAFE，因为存在潜在的内存泄漏（即“可能在中止时泄漏”的主机保护属性）。 - Solomon Rutzky这个解决方案似乎太显而易见了。
如果你使用 GETUTCDATE() 函数可以获得协调世界时（UTC）日期，而使用 GETDATE() 函数可以获得本地日期，那么你就可以得到一个偏移量，用于任何日期时间的计算。
SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, GETUTCDATE())
这应该返回您执行查询时的本地时间。
SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, N'1/14/2011 7:00:00' )
这将返回2011-01-14 02:00:00.000，因为我在UTC +5时区。
除非我漏掉了什么？
虽然问题的标题提到了SQL Server 2005，但标签是关于SQL Server的普遍标签。 对于SQL Server 2016及以后版本，您可以使用以下方法：
SELECT yourUtcDateTime AT TIME ZONE 'Mountain Standard Time'
可以使用SELECT * FROM sys.time_zone_info获取时区列表。
CREATE FUNCTION [dbo].[fnGetServerTimeFromUTC]
(
@AppointmentDate AS DATETIME,
@DateTimeOffset DATETIMEOFFSET
)
RETURNS DATETIME
AS
BEGIN
--DECLARE @AppointmentDate DATETIME;
--SET @AppointmentDate = '2016-12-01 12:00:00'; SELECT @AppointmentDate;
--Get DateTimeOffset from Server
--DECLARE @DateTimeOffset; SET @DateTimeOffset = SYSDATETIMEOFFSET();
DECLARE @DateTimeOffsetStr NVARCHAR(34) = @DateTimeOffset;
--Set a standard DatePart value for Sunday (server configuration agnostic)
DECLARE @dp_Sunday INT = 7 - @@DATEFIRST + 1;
--2006 DST Start First Sunday in April (earliest is 04-01) Ends Last Sunday in October (earliest is 10-25)
--2007 DST Start Second Sunday March (earliest is 03-08) Ends First Sunday Nov (earliest is 11-01)
DECLARE @Start2006 NVARCHAR(6) = '04-01-';
DECLARE @End2006 NVARCHAR(6) = '10-25-';
DECLARE @Start2007 NVARCHAR(6) = '03-08-';
DECLARE @End2007 NVARCHAR(6) = '11-01-';
DECLARE @ServerDST SMALLINT = 0;
DECLARE @ApptDST SMALLINT = 0;
DECLARE @Start DATETIME;
DECLARE @End DATETIME;
DECLARE @CurrentMinuteOffset INT;
DECLARE @str_Year NVARCHAR(4) = LEFT(@DateTimeOffsetStr,4);
DECLARE @Year INT = CONVERT(INT, @str_Year);
SET @CurrentMinuteOffset = CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,29,3)) * 60 + CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,33,2)); --Hours + Minutes
--Determine DST Range for Server Offset
SET @Start = CASE
WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')
ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')
END;
WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN
SET @Start = DATEADD(DAY, 1, @Start)
END;
SET @End = CASE
WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')
ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')
END;
WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN
SET @End = DATEADD(DAY, 1, @End)
END;
--Determine Current Offset based on Year
IF @DateTimeOffset >= @Start AND @DateTimeOffset < @End SET @ServerDST = 1;
--Determine DST status of Appointment Date
SET @Year = YEAR(@AppointmentDate);
SET @Start = CASE
WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')
ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')
END;
WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN
SET @Start = DATEADD(DAY, 1, @Start)
END;
SET @End = CASE
WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')
ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')
END;
WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN
SET @End = DATEADD(DAY, 1, @End)
END;
--Determine Appointment Offset based on Year
IF @AppointmentDate >= @Start AND @AppointmentDate < @End SET @ApptDST = 1;
SET @AppointmentDate = DATEADD(MINUTE, @CurrentMinuteOffset + 60 * (@ApptDST - @ServerDST), @AppointmentDate)
RETURN @AppointmentDate
END
GO
对于那些被困在SQL Server 2005中且不想或无法使用UDF的人 - 特别是在美国以外的地区 - 我采用了@Bobman的方法并将其泛化。以下内容适用于美国、欧洲、新西兰和澳大利亚，但需要注意的是，即使处于同一“基本”时区的澳大利亚州也不是所有州都遵守夏令时。添加尚未支持的DST规则也很容易，只需在@calculation值中添加一行即可。
-- =============================================
-- Author: Herman Scheele
-- Create date: 20-08-2016
-- Description: Convert UTC datetime to local datetime
-- based on server time-distance from utc.
-- =============================================
create function dbo.UTCToLocalDatetime(@UTCDatetime datetime)
returns datetime as begin
declare @LocalDatetime datetime, @DSTstart datetime, @DSTend datetime
declare @calculation table (
frm smallint,
til smallint,
since smallint,
firstPossibleStart datetime,-- Put both of these with local non-DST time!
firstPossibleEnd datetime -- (In Europe we turn back the clock from 3 AM to 2 AM, which means it happens 2 AM non-DST time)
)
insert into @calculation
values
(-9, -2, 1967, '1900-04-24 02:00', '1900-10-25 01:00'), -- USA first DST implementation
(-9, -2, 1987, '1900-04-01 02:00', '1900-10-25 01:00'), -- USA first DST extension
(-9, -2, 2007, '1900-03-08 02:00', '1900-11-01 01:00'), -- USA second DST extension
(-1, 3, 1900, '1900-03-25 02:00', '1900-10-25 02:00'), -- Europe
(9.5,11, 1971, '1900-10-01 02:00', '1900-04-01 02:00'), -- Australia (not all Aus states in this time-zone have DST though)
(12, 13, 1974, '1900-09-24 02:00', '1900-04-01 02:00') -- New Zealand
select top 1 -- Determine if it is DST /right here, right now/ (regardless of input datetime)
@DSTstart = dateadd(year, datepart(year, getdate())-1900, firstPossibleStart), -- Grab first possible Start and End of DST period
@DSTend = dateadd(year, datepart(year, getdate())-1900, firstPossibleEnd),
@DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),-- Shift Start and End of DST to first sunday
@DSTend = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
@LocalDatetime = dateadd(hour, datediff(hour, getutcdate(), getdate()), @UTCDatetime), -- Add hours to current input datetime (including possible DST hour)
@LocalDatetime = case
when frm < til and getdate() >= @DSTstart and getdate() < @DSTend -- If it is currently DST then we just erroneously added an hour above,
or frm > til and (getdate() >= @DSTstart or getdate() < @DSTend) -- substract 1 hour to get input datetime in current non-DST timezone,
then dateadd(hour, -1, @LocalDatetime) -- regardless of whether it is DST on the date of the input datetime
else @LocalDatetime
end
from @calculation
where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
and datepart(year, getdate()) >= since
order by since desc
select top 1 -- Determine if it was/will be DST on the date of the input datetime in a similar fashion
@DSTstart = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleStart),
@DSTend = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleEnd),
@DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),
@DSTend = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
@LocalDatetime = case
when frm < til and @LocalDatetime >= @DSTstart and @LocalDatetime < @DSTend -- If it would be DST on the date of the input datetime,
or frm > til and (@LocalDatetime >= @DSTstart or @LocalDatetime < @DSTend) -- add this hour to the input datetime.
then dateadd(hour, 1, @LocalDatetime)
else @LocalDatetime
end
from @calculation
where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
and datepart(year, @LocalDatetime) >= since
order by since desc
return @LocalDatetime
end
此函数在运行时查看本地时间和协调世界时之间的差异，以确定应用哪些夏令时规则。然后它知道是否包含夏令时小时并将其减去。然后它确定输入UTC日期时间的日期是否为夏令时，并在此情况下添加夏令时小时。
这带来一个小问题，即在夏令时的最后一小时和非夏令时的第一小时，该函数无法确定是哪个，并假定是后者。因此，无论输入日期时间如何，如果此代码在夏令时的最后一小时运行，则会给出错误的结果。这意味着它99.9886%的时间可以正常工作。
AT TIME ZONE。谢谢。 - Matt Johnson-PintBobman的答案接近正确，但有几个错误： 1）您必须比较本地夏令时（而不是本地标准时间）与夏令时结束日期时间。 2）SQL BETWEEN是包容性的，因此您应该使用“>=和<”进行比较，而不是BETWEEN。
这里是一个工作的修改版本以及一些测试用例： （再次强调，这仅适用于美国）
-- Test cases:
-- select dbo.fn_utc_to_est_date('2016-03-13 06:59:00.000') -- -> 2016-03-13 01:59:00.000 (Eastern Standard Time)
-- select dbo.fn_utc_to_est_date('2016-03-13 07:00:00.000') -- -> 2016-03-13 03:00:00.000 (Eastern Daylight Time)
-- select dbo.fn_utc_to_est_date('2016-11-06 05:59:00.000') -- -> 2016-11-06 01:59:00.000 (Eastern Daylight Time)
-- select dbo.fn_utc_to_est_date('2016-11-06 06:00:00.000') -- -> 2016-11-06 01:00:00.000 (Eastern Standard Time)
CREATE FUNCTION [dbo].[fn_utc_to_est_date]
(
@utc datetime
)
RETURNS datetime
as
begin
-- set offset in standard time (WITHOUT daylight saving time)
declare @offset smallint
set @offset = -5 --EST
declare @localStandardTime datetime
SET @localStandardTime = dateadd(hh, @offset, @utc)
-- DST in USA starts on the second sunday of march and ends on the first sunday of november.
-- DST was extended beginning in 2007:
-- https://en.wikipedia.org/wiki/Daylight_saving_time_in_the_United_States#Second_extension_.282005.29
-- If laws/rules change, obviously the below code needs to be updated.
declare @dstStartDate datetime,
@dstEndDate datetime,
@year int
set @year = datepart(year, @localStandardTime)
-- get the first possible DST start day
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'
while ((datepart(weekday,@dstStartDate) != 1)) begin --while not sunday
set @dstStartDate = dateadd(day, 1, @dstStartDate)
end
-- get the first possible DST end day
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'
while ((datepart(weekday,@dstEndDate) != 1)) begin --while not sunday
set @dstEndDate = dateadd(day, 1, @dstEndDate)
end
declare @localTimeFinal datetime,
@localTimeCompare datetime
-- if local date is same day as @dstEndDate day,
-- we must compare the local DAYLIGHT time to the @dstEndDate (otherwise we compare using local STANDARD time).
-- See: http://www.timeanddate.com/time/change/usa?year=2016
if (datepart(month,@localStandardTime) = datepart(month,@dstEndDate)
and datepart(day,@localStandardTime) = datepart(day,@dstEndDate)) begin
set @localTimeCompare = dateadd(hour, 1, @localStandardTime)
end
else begin
set @localTimeCompare = @localStandardTime
end
set @localTimeFinal = @localStandardTime
-- check for DST
if (@localTimeCompare >= @dstStartDate and @localTimeCompare < @dstEndDate) begin
set @localTimeFinal = dateadd(hour, 1, @localTimeFinal)
end
return @localTimeFinal
end
AT TIME ZONE。谢谢。 - Matt Johnson-PintGETUTCDATE()只会给你当前的UTC时间，对这个值进行任何DATEADD()操作都不会包括任何夏令时转换。
最好的方法是建立自己的UTC转换表或者使用类似以下的方法：
http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx
最近我也需要做同样的事情。诀窍在于找出与UTC的偏移量，但这并不是很难的。你只需使用DateDiff来获取本地时间和UTC之间的小时差异即可。我写了一个函数来处理这个问题。
Create Function ConvertUtcDateTimeToLocal(@utcDateTime DateTime)
Returns DateTime
Begin
Declare @utcNow DateTime
Declare @localNow DateTime
Declare @timeOffSet Int
-- Figure out the time difference between UTC and Local time
Set @utcNow = GetUtcDate()
Set @localNow = GetDate()
Set @timeOffSet = DateDiff(hh, @utcNow, @localNow)
DECLARE @localTime datetime
Set @localTime = DateAdd(hh, @timeOffset, @utcDateTime)
-- Check Results
return @localTime
End
GO
这确实有一个关键的缺陷：如果一个时区使用分数偏移，比如尼泊尔是GMT+5:45，那么这将失败，因为它只处理整小时。不过，它应该完全符合您的需求。