Вам когда-нибудь приходилось преобразовывать локальное время в UTC в SQL Server? Если вы используете SQL Server 2012 или более раннюю версию, вы могли заметить, что для этой задачи нет встроенной функции. В этой статье мы рассмотрим подход T-SQL для преобразования локального времени в UTC без использования CLR.

Недавно я работал с клиентом, который все еще использовал SQL Server 2012. Они нуждались в преобразовании локальных дат и времени в UTC, но функция AT TIME ZONE, введенная в более поздней версии SQL Server, для них не была доступна. После некоторого исследования я наткнулся на скрипт на StackOverflow, который предоставляет чистое решение на T-SQL.

Скрипт использует CTE (Общее выражение таблицы) для вычисления начальной и конечной дат перехода на летнее время для заданного года. Затем он определяет, попадает ли предоставленное локальное время в период летнего времени и рассчитывает смещение соответственно. Наконец, он преобразует локальное время в UTC.

Вот пример скрипта:

CREATE FUNCTION dbo.ConvertLocalDateToUTC(@localDate DATETIME)
RETURNS TABLE
AS
RETURN
WITH cte_dstperiod AS
(
    -- Вычислить начальную и конечную даты перехода на летнее время
    SELECT
        dststart = DATEADD(HOUR, 2, DATEADD(DAY, 1 - DATEPART(WEEKDAY, DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))), DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0))))),
        dstend = DATEADD(HOUR, 2, DATEADD(DAY, 1 - DATEPART(WEEKDAY, DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))), DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))))
),
cte_dst AS
(
    -- Определить, находится ли предоставленная дата в периоде летнего времени
    SELECT
        dst = IIF(@localDate >= dststart AND @localDate < dstend, 1, 0),
        offset = +60 -- CET = UTC + 1 (60 минут)
    FROM cte_dstperiod
),
cte_offset AS
(
    -- Рассчитать смещение и преобразовать локальное время в соответствующий часовой пояс
    SELECT
        dstFlag = dst,
        offset,
        localdate = @localDate,
        localdateTZ = IIF(dst = 1, TODATETIMEOFFSET(@localDate, offset + 60), TODATETIMEOFFSET(@localDate, offset))
    FROM cte_dst
)
SELECT
    dstFlag,
    offset,
    localdate,
    localdateTZ,
    UTCdate = CONVERT(DATETIME, localdateTZ, 1)
FROM cte_offset;

Скрипт является встроенной функцией с возвращаемой таблицей, что означает, что его можно использовать в запросе, как обычную таблицу. Он принимает локальную дату и время в качестве входного параметра и возвращает соответствующую дату и время в UTC.

В скрипте сделано несколько предположений. Во-первых, он предполагает, что настройка системы datefirst установлена на 7, что соответствует воскресенью. Это значение по умолчанию в большинстве систем, но если ваша система использует другой первый день недели, вам может потребоваться изменить логику в скрипте.

Во-вторых, скрипт предполагает, что переход на летнее время начинается в последнее воскресенье марта и заканчивается в последнее воскресенье октября. Если ваш регион следует другому графику перехода на летнее время, вы можете соответствующим образом адаптировать скрипт или создать таблицу-справочник со всеми необходимыми датами начала и окончания.

Используя этот скрипт, вы можете преобразовывать локальные даты и время в UTC в SQL Server 2012 или более ранних версиях без использования CLR. Он предоставляет чистое решение на T-SQL, которое эффективно и легко в использовании.

Вы можете найти полный скрипт на GitHub.

Спасибо за чтение! Я надеюсь, что вы найдете эту статью полезной для понимания, как преобразовывать локальные даты и время в UTC в SQL Server.