Недавно я обновил свою систему для записи даты/времени в формате UTC, как ранее они сохранялись в качестве локального времени.
Теперь мне нужно преобразовать всю локальную сохраненную дату/время в UTC. Мне было интересно, есть ли встроенная функция, аналогичная методу .NET ConvertTime?
Я пытаюсь избежать необходимости писать приложение-утилиту, чтобы сделать это для меня.
Любые предложения?
Если они все локальны для вас, то вот смещение:
SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime
и вы сможете обновить все данные, используя:
UPDATE SomeTable
SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)
Будет ли это работать, или мне не хватает другого угла этой проблемы?
Я не верю, что приведенный выше код будет работать. Причина в том, что это зависит от разницы между текущей датой в местном и UTC раз. Например, здесь, в Калифорнии, мы сейчас находимся в PDT (Pacific Daylight Time); разница между этим временем и UTC составляет 7 часов. Предоставленный код будет, если будет выполняться сейчас, добавить 7 часов к каждой дате, которую необходимо преобразовать. Но если историческая сохраненная дата или дата в будущем преобразуются, и эта дата не выполняется во время летнего времени, она все равно добавит 7, когда правильное смещение равно 8. Нижняя строка: вы не можете правильно преобразовать дату/время между часовыми поясами (включая UTC, который не соблюдает летнее время), только глядя на текущую дату. Вы должны сами рассмотреть дату, которую вы конвертируете, относительно того, действовало ли дневное время в эту дату. Кроме того, даты, в которые изменились дневной свет и стандартные времена, изменились (Джордж Буш изменил даты во время своей администрации для США!). Другими словами, любое решение, которое даже ссылается на getdate() или getutcdate(), не работает. Он должен проанализировать фактическую дату, которую нужно преобразовать.
В 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 с нулевым смещением.
Как уже упоминалось ранее, не существует встроенного способа выполнения преобразования даты в режиме часового пояса в SQL Server (по крайней мере, с SQL Server 2012).
У вас есть по существу три варианта, чтобы сделать это правильно:
В то время как SQL Server не предлагает инструменты для выполнения преобразования даты в режиме часовых поясов, платформа .NET работает, и до тех пор, пока вы можете использовать SQL CLR, вы можете воспользоваться этим.
В Visual Studio 2012 убедитесь, что у вас установлены инструменты для данных (в противном случае проект SQL Server не будет отображаться в качестве опции) и создайте новый проект SQL Server.
Затем добавьте новую SQL CLR С# User Defined Function, назовите ее "ConvertToUtc". VS будет генерировать плиту котла для вас, которая должна выглядеть примерно так:
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ConvertToUtc()
{
// Put your code here
return new SqlString (string.Empty);
}
}
Мы хотим внести здесь несколько изменений. Во-первых, мы хотим вернуть a SqlDateTime, а не a 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. Щелкните правой кнопкой мыши проект базы данных и выберите "Опубликовать". Настройте соединение и имя базы данных, а затем нажмите "Опубликовать", чтобы вставить код в базу данных или нажмите "Создать Script", если вы хотите сохранить script для потомков (или вставить биты в производство).
Как только у вас есть UDF в базе данных, вы можете увидеть его в действии:
declare @dt as datetime
set @dt = '12/1/2013 1:00 pm'
select dbo.ConvertToUtc(@dt)
Вот протестированная процедура, которая обновила мою базу данных от локального до 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
Обновление Script
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
Если вам нужно преобразовать даты, отличные от сегодняшних, к разным часовым поясам, вам приходится иметь дело с летней экономией. Мне нужно решение, которое можно было бы сделать, не беспокоясь о версии базы данных, без использования хранимых функций и чего-то, что можно было бы легко портировать в Oracle.
Я думаю, что Уоррен находится на правильном пути с получением правильных дат для дневного света, но чтобы сделать его более полезным для нескольких часовых поясов и разных правил для стран и даже правила, которое изменилось в США в период с 2006 по 2007 год, здесь вариант решения. Обратите внимание, что у нас есть не только часовые пояса, но и центральная Европа. Центральная Европа следует за последним воскресеньем апреля и в последнее воскресенье октября. Вы также заметите, что США в 2006 году следует за старым первым воскресеньем в апреле, последнее воскресенье в октябре.
Этот код SQL может выглядеть немного уродливым, но просто скопируйте и вставьте его в SQL Server и попробуйте. Обратите внимание, что существует 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 '2018' 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.
Я помещаю вышеуказанный запрос в представление. Теперь, в любое время, когда мне нужна дата со смещением часового пояса или конвертирована в 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'
Вот моя быстрая и грязная версия. Я знаю, что все мои даты использовались в восточном часовом поясе США. Вы можете изменить смещение или иначе сделать его более умным, как вам нужно. Я делал одноразовую миграцию, так что это было достаточно хорошо.
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
Если я не пропустил что-то выше (возможно), все вышеприведенные методы ошибочны в том, что они не принимают перекрытие при переключении с летнего сбережения (например, EDT) на стандартное время (например, EST). A (очень многословный) пример:
[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
Простые смещения часа, основанные на дате и времени, не сократят его. Если вы не знаете, было ли местное время записано в EDT или EST с 01:00 до 01:59, у вас не будет подсказки! Например, используйте 01:30: если вы найдете более поздние времена в диапазоне от 01:31 до 01:59 до этого, вы не будете знать, есть ли в 01:30, который вы смотрите, [3 или [6. В этом случае вы можете получить правильное время UTC с небольшим количеством кодировок, чтобы смотреть на предыдущие записи (не весело в SQL), и это самый лучший случай...
Скажите, что у вас записано следующее местное время, и не выделял бит для указания 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
Времена [2] и [3] могут находиться в таймфрейме 5 AM, таймфрейме 6 AM или одном в 5 AM, а другой - в 6 AM таймфреймах., Другими словами: вы попадаете и должны выкидывать все показания между 01:00:00 и 01:59:59. В этом случае нет абсолютно никакого способа решить фактическое время UTC!
Следующее должно работать, поскольку оно вычисляет разницу между DATE и UTCDATE для сервера, на котором вы работаете, и использует это смещение для вычисления эквивалента UTC любой даты, которую вы передаете ему. В моем примере я пытаюсь преобразовать UTC-эквивалент для "1-но-2012-го 06:00" в Аделаиде, Австралия, где Смещение UTC составляет -630 минут, что при добавлении к любой дате приведет к эквиваленту UTC любой местной даты.
выберите DATEADD (MINUTE, DATEDIFF (MINUTE, GETDATE(), GETUTCDATE()), '1-но-2012 06:00')
В зависимости от того, как далеко назад вам нужно идти, вы можете создать таблицу летнего времени, а затем присоединиться к таблице и выполнить 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
Мы можем конвертировать ServerZone DateTime в UTC и UTC на ServerZone DateTime
Просто запустите следующие скрипты, чтобы понять преобразование, а затем измените как что вам нужно
--Get Server 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 +, и это преимущество автоматически учитывает Daylight при преобразовании в конкретный часовой пояс
Ответ Мэтта Джонсона - абсолютно лучший подход в Microsoft SQL Server 2016+. Однако его предложение использовать AT TIME ZONE имеет одну уязвимость, о которой следует знать. Невозможно определить правильное смещение от UTC для часа, когда заканчивается летнее время. Насколько я знаю, нет никакого способа решить эту проблему. Рассмотрим пример ниже.
--Assume that the value of input_date = '2018-11-04 01:00:00'
--Assume that dates in this field are written in Central Standard Time
select input_date at time zone 'Central Standard Time'
>> '2018-11-04 01:00:00 -05:00'
Поведение Microsoft SQL Server по умолчанию предполагает, что смещение значения input_date равно -05: 00. Однако это время может фактически представлять смещение -05: 00 ИЛИ -06: 00, в зависимости от того, закончился ли DST или нет. Не существует способа узнать наверняка без сопутствующего смещения, поэтому Microsoft SQL Server делает предположение, которое может быть или не быть правильным.
В случае, если кто-то наткнется на это и использует ответ Криса Барлоу, там будет опечатка, где 2018 указан дважды, а второй раз должен быть 2019. В частности, эта строка:
UNION select '2017' yr UNION select '2018' yr UNION select '2018' yr UNION select '2020' yr UNION select '2021' yr
Извините, у меня недостаточно очков, чтобы комментировать ответ Криса. Кроме этого, ответ отличный!