Como posso obter o deslocamento correto entre o UTC e o horário local para uma data anterior ou posterior ao horário de verão?

A melhor maneira de converter uma data UTC não atual em hora local, anterior ao SQL Server 2016, é usar o Microsoft .Net Common Language Runtime, ou CLR.

O código em si é fácil; a parte difícil geralmente é convencer as pessoas de que o CLR não é puro mal ou assustador...

Para um dos muitos exemplos, confira a postagem do blog de Harsh Chawla sobre o tópico .

Infelizmente, não há nada integrado antes do SQL Server 2016 que possa lidar com esse tipo de conversão, exceto soluções baseadas em CLR. Você poderia escrever uma função T-SQL que faça algo assim, mas então você teria que implementar a lógica de mudança de data por conta própria, e eu chamaria isso decididamente de não fácil.

Desenvolvi e publiquei o projeto T-SQL Toolbox no codeplex para ajudar qualquer pessoa que tenha dificuldades com a manipulação de data e hora e fuso horário no Microsoft SQL Server. É de código aberto e totalmente gratuito para usar.

Ele oferece UDFs de conversão de data e hora fáceis usando T-SQL simples (sem CLRs), além de tabelas de configuração pré-preenchidas prontas para uso. E tem suporte completo para DST (horário de verão).

Uma lista de todos os fusos horários com suporte pode ser encontrada na tabela "DateTimeUtil.Timezone" (fornecida no banco de dados T-SQL Toolbox).

No seu exemplo, você pode usar o seguinte exemplo:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

Isso retornará o valor de data e hora local convertido.

Infelizmente, há suporte para SQL Server 2008 ou posterior apenas devido aos tipos de dados mais recentes (DATE, TIME, DATETIME2). Mas como o código-fonte completo é fornecido, você pode ajustar facilmente as tabelas e UDFs substituindo-as por DATETIME. Não tenho um MSSQL 2005 disponível para teste, mas deve funcionar com o MSSQL 2005 também. Em caso de dúvidas, é só avisar.

Eu sempre uso este comando TSQL.

-- the utc value 
declare @utc datetime = '20/11/2014 05:14'

-- the local time

select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)

-- or if you're concerned about non-whole-hour offsets, use:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc).

É muito simples e faz o trabalho.

Para SQL Server 2016+, você pode usar AT TIME ZONE . Ele irá lidar automaticamente com os horários de verão.

Encontrei esta resposta no StackOverflow que fornece uma função definida pelo usuário que parece traduzir com precisão os datetimes

A única coisa que você precisa modificar é a @offsetvariável na parte superior para defini-la para o deslocamento de fuso horário do servidor SQL que executa essa função. No meu caso, nosso servidor SQL usa EST, que é GMT - 5

Não é perfeito e provavelmente não funcionará para muitos casos, como deslocamentos TZ de meia hora ou 15 minutos (para aqueles que eu recomendaria uma função CLR como Kevin recomendado ), no entanto, funciona bem o suficiente para a maioria dos fusos horários genéricos no Norte América.

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

Existem algumas boas respostas para uma pergunta semelhante feita no Stack Overflow. Acabei usando uma abordagem T-SQL da segunda resposta de Bob Albright para limpar uma bagunça causada por um consultor de conversão de dados.

Funcionou para quase todos os nossos dados, mas depois percebi que seu algoritmo só funciona para datas de 5 de abril de 1987 , e tivemos algumas datas da década de 1940 que ainda não foram convertidas corretamente. Em última análise, precisávamos que as UTCdatas em nosso banco de dados SQL Server se alinhassem com um algoritmo em um programa de terceiros que usava a API Java para converter de UTChora local.

Eu gosto do CLRexemplo na resposta de Kevin Feasel usando o exemplo de Harsh Chawla, e também gostaria de compará-lo com uma solução que usa Java, já que nosso front-end usa Java para fazer a UTCconversão para hora local.

A Wikipedia menciona 8 emendas constitucionais diferentes que envolvem ajustes de fuso horário anteriores a 1987, e muitas delas são muito localizadas em diferentes estados, então há uma chance de que o CLR e o Java possam interpretá-las de maneira diferente. O código do seu aplicativo front-end usa dotnet ou Java, ou as datas anteriores a 1987 são um problema para você?

Você pode fazer isso facilmente com um procedimento armazenado CLR.

[SqlFunction]
public static SqlDateTime ToLocalTime(SqlDateTime UtcTime, SqlString TimeZoneId)
{
    if (UtcTime.IsNull)
        return UtcTime;

    var timeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneId.Value);
    var localTime = TimeZoneInfo.ConvertTimeFromUtc(UtcTime.Value, timeZone);
    return new SqlDateTime(localTime);
}

Você pode armazenar os fusos horários disponíveis em uma tabela:

CREATE TABLE TimeZones
(
    TimeZoneId NVARCHAR(32) NOT NULL CONSTRAINT PK_TimeZones PRIMARY KEY,
    DisplayName NVARCHAR(64) NOT NULL,
    SupportsDaylightSavingTime BIT NOT NULL,
)

E esse procedimento armazenado preencherá a tabela com os possíveis fusos horários em seu servidor.

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void PopulateTimezones()
    {
        using (var sql = new SqlConnection("Context Connection=True"))
        {
            sql.Open();

            using (var cmd = sql.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TimeZones";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO [dbo].[TimeZones]([TimeZoneId], [DisplayName], [SupportsDaylightSavingTime]) VALUES(@TimeZoneId, @DisplayName, @SupportsDaylightSavingTime);";
                var Id = cmd.Parameters.Add("@TimeZoneId", SqlDbType.NVarChar);
                var DisplayName = cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar);
                var SupportsDaylightSavingTime = cmd.Parameters.Add("@SupportsDaylightSavingTime", SqlDbType.Bit);

                foreach (var zone in TimeZoneInfo.GetSystemTimeZones())
                {
                    Id.Value = zone.Id;
                    DisplayName.Value = zone.DisplayName;
                    SupportsDaylightSavingTime.Value = zone.SupportsDaylightSavingTime;

                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

O SQL Server versão 2016 resolverá esse problema de uma vez por todas . Para versões anteriores, uma solução CLR provavelmente é mais fácil. Ou para uma regra de horário de verão específica (como apenas nos EUA), uma função T-SQL pode ser relativamente simples.

No entanto, acho que uma solução T-SQL genérica pode ser possível. Desde que xp_regreadfuncione, tente isso:

CREATE TABLE #tztable (Value varchar(50), Data binary(56));
DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
INSERT INTO #tztable
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
SELECT                                                                                  -- See http://msdn.microsoft.com/ms725481
 CAST(CAST(REVERSE(SUBSTRING(Data,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
 CAST(CAST(REVERSE(SUBSTRING(Data,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
 CAST(CAST(REVERSE(SUBSTRING(Data,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
 -- When DST ends:
 CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear,       -- 0 = yearly (else once)
 CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth,      -- 0 = no DST
 CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek,  -- 0 = Sunday to 6 = Saturday
 CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek,       -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
 CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour,       -- Local time
 CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
 -- When DST starts:
 CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear,       -- See above
 CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
 CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
 CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
FROM #tztable;
DROP TABLE #tztable

Uma função T-SQL (complexa) pode usar esses dados para determinar o deslocamento exato para todas as datas durante a regra de horário de verão atual.

Aqui está uma resposta escrita para um aplicativo específico do Reino Unido e baseada puramente em SELECT.

1. Sem deslocamento de fuso horário (por exemplo, Reino Unido)
2. Escrito para o horário de verão começando no último domingo de março e terminando no último domingo de outubro (regras do Reino Unido)

3. Não aplicável entre meia-noite e 1h no início do horário de verão. Isso pode ser corrigido, mas o aplicativo para o qual foi escrito não exige isso.

   -- A variable holding an example UTC datetime in the UK, try some different values:
   DECLARE
   @App_Date datetime;
   set @App_Date = '20250704 09:00:00'
   
   -- Outputting the local datetime in the UK, allowing for daylight saving:
   SELECT
   case
   when @App_Date >= dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0))))
       and @App_Date < dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0))))
       then DATEADD(hour, 1, @App_Date) 
   else @App_Date 
   end
   

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
SELECT @TimeZone
DECLARE @someUtcTime DATETIME
SET @someUtcTime = '2017-03-05 15:15:15'
DECLARE @TimeBiasAtSomeUtcTime INT
SELECT @TimeBiasAtSomeUtcTime = DATEDIFF(MINUTE, @someUtcTime, @someUtcTime AT TIME ZONE @TimeZone)
SELECT DATEADD(MINUTE, @TimeBiasAtSomeUtcTime * -1, @someUtcTime)