Ответ 1

Я понял это. Фокус в том, что есть встроенная функция SQL Server ToDateTimeOffset, которая прикрепляет произвольную информацию о смещении к любому поставляемому datetime.

Например, идентичные запросы:

SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240)
SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00')

оба возвращаются:

2013-07-25 15:35:27.0000000 -04:00

Примечание. Параметр offset для ToDateTimeOffset может быть:

• a integer, представляющий несколько минут
• a string, представляющий часы и минуты (в формате {+|-}TZH:THM)

Нам нужен текущий UTC-адрес сервера

Далее нам нужно смещение текущего сервера от UTC. Я два способа, которым SQL Server может вернуть число integer минут из UTC:

DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) 
DATEDIFF(minute, GETUTCDATE(), GETDATE())

оба возвращаются

-240

Включение этого в функцию ToDateTimeOffset:

SELECT ToDateTimeOffset(
      '2013-07-25 15:35:27',
      DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) --e.g. -240
)

возвращает значение datetimeoffset, которое я хочу:

2013-07-25 15:35:27.0000000 -04:00

Вводя его вообще

Теперь мы можем иметь лучшую функцию для преобразования даты и времени в datetimeoffset:

CREATE FUNCTION dbo.ToDateTimeOffset(@value datetime2)
    RETURNS datetimeoffset AS
BEGIN
/*
    Converts a date/time without any timezone offset into a datetimeoffset value, 
    using the server current offset from UTC. 

    For this we use the builtin ToDateTimeOffset function; 
    which attaches timezone offset information with a datetimeoffset value.

    The trick is to use DATEDIFF(minutes) between local server time and UTC 
    to get the offset parameter.

    For example:
        DATEPART(TZOFFSET, SYSDATETIMEOFFSET())
    returns the integer
        -240

    for people in EDT (Eastern Daylight Time), which is 4 hours (240 minutes) behind UTC.
    Pass that value to the SQL Server function:
        TODATETIMEOFFSET(@value, -240)
*/

    RETURN TODATETIMEOFFSET(@value, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))
END;

Использование образца

SELECT TOP 5
    ChangeDate, 
    dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset
FROM AuditLog

возвращает желаемое значение:

ChangeDate               ChangeDateOffset
=======================  ==================================
2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 -04:00
2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 -04:00
2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 -04:00
2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 -04:00
2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 -04:00

Было бы идеально, если бы встроенная функция просто сделала это:

TODATETIMEOFFSET(value)

вместо необходимости создавать "перегрузку":

dbo.ToDateTimeOffset(value)

Примечание. Любой код выпущен в общедоступном домене. Не требуется атрибуция.

Ответ 2

Для преобразования из локального времени в datetimeoffset с текущим смещением времени, кажется, требуется некоторое обман. Вероятно, это более простой способ, но это похоже на это:

SELECT ChangeDate, 
  CONVERT(DATETIMEOFFSET, CONVERT(VARCHAR, ChangeDate, 120) + 
          RIGHT(CONVERT(VARCHAR, SYSDATETIMEOFFSET(), 120), 6), 120)
FROM AuditLog;

Возможно, стоит создать функцию;

CREATE FUNCTION LOCALIFY(@dt DATETIME) 
  RETURNS DATETIMEOFFSET AS
BEGIN
 RETURN CONVERT(DATETIMEOFFSET, 
          CONVERT(VARCHAR, @dt, 120) + 
          RIGHT(CONVERT(VARCHAR, SYSDATETIMEOFFSET(), 120), 6), 120)
END;

... и затем просто...

SELECT ChangeDate, dbo.LOCALIFY(ChangeDate) FROM AuditLog;

Ответ 3

Это немного позже во времени от OP, но этот поток полезен при распознавании методов преобразования datetime в datetimeoffset.

Я использовал некоторые функции, но также предложил бы использовать поле с настройкой по умолчанию sysdatetimeoffset(), так что, поскольку элементы были вставлены (текущая временная метка), были бы относительно того, когда они были помещены. Тогда if необходимы модификации, обновление может использовать TZ из источника в процедуре.

Это особенно заметно в транзакциях OData v4, для которых требуется datetimeoffset.

Ответ 4

Я думаю, вам нужно умножить DATEPART(TZOFFSET,SYSDATETIMEOFFSET()) на -1, чтобы получить правильное смещение TimeZone. Я думаю, если вы находитесь в восточном часовом поясе, смещение TimeZone должно быть +4: 00 вместо -4: 00. Является ли смещение с моего локального сервера на UTC или с UTC на локальный сервер?