TSQL: Como converter a hora local para UTC? (SQL Server 2008)

Estamos lidando com um aplicativo que precisa manipular dados de hora global de diferentes fusos horários e configurações de horário de verão. A idéia é armazenar tudo em formato UTC internamente e só converter para frente e para trás para as interfaces de usuário localizadas. O SQL Server oferece algum mecanismo para lidar com as traduções dadas uma vez, um país e um fuso horário?

Isso deve ser um problema comum, então estou surpreso que o Google não mostre nada utilizável.

Alguma ponteira?

7 anos se passaram e …
Na verdade, há esse novo recurso do SQL Server 2016 que faz exatamente o que você precisa.
Ele é chamado de AT TIME ZONE e converte a data em um fuso horário especificado, considerando as alterações do horário de verão.
Mais informações aqui: https://msdn.microsoft.com/pt-br/library/mt612795.aspx

Isso funciona para datas que atualmente têm o mesmo deslocamento de UTC que o host do SQL Server; não conta para mudanças de horário de verão. Substitua YOUR_DATE pela data local para conversão.

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);

Embora algumas dessas respostas levem você ao ponto, você não pode fazer o que está tentando fazer com datas arbitrárias para o SqlServer 2005 e versões anteriores, devido ao horário de verão. Usar a diferença entre o UTC local e atual atual me dará o deslocamento como existe hoje. Não encontrei uma maneira de determinar qual seria o deslocamento para a data em questão.

Dito isso, sei que o SqlServer 2008 fornece algumas novas funções de data que podem resolver esse problema, mas as pessoas que usam uma versão anterior precisam estar cientes das limitações.

Nossa abordagem é manter o UTC e executar a conversão no lado do cliente, onde temos mais controle sobre a precisão da conversão.

O SQL Server 2008 tem um tipo chamado datetimeoffset . É muito útil para esse tipo de coisa.

http://msdn.microsoft.com/pt-br/library/bb630289.aspx

Em seguida, você pode usar a function SWITCHOFFSET para movê-la de um fuso horário para outro, mas ainda mantendo o mesmo valor UTC.

http://msdn.microsoft.com/pt-br/library/bb677244.aspx

Roubar

Você pode usar meu projeto de suporte a fuso horário do SQL Server para converter entre os fusos horários padrão da IANA, conforme listado aqui .

UTC para Local é assim:

 SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles') 

Local para UTC é assim:

 SELECT Tzdb.LocalToUtc('2015-07-01 00:00:00', 'America/Los_Angeles', 1, 1) 

As opções numéricas são sinalizadores para controlar o comportamento quando os valores de hora local são afetados pelo horário de verão. Estes são descritos detalhadamente na documentação do projeto.

Eu tenho a tendência de usar DateTimeOffset para todo o armazenamento de data e hora que não está relacionado a um evento local (por exemplo: reunião / festa, etc, 12: 00-15: 00 no museu).

Para obter o DTO atual como UTC:

 DECLARE @utcNow DATETIMEOFFSET = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) DECLARE @utcToday DATE = CONVERT(DATE, @utcNow); DECLARE @utcTomorrow DATE = DATEADD(D, 1, @utcNow); SELECT @utcToday [today] ,@utcTomorrow [tomorrow] ,@utcNow [utcNow] 

OBSERVAÇÃO: sempre usarei o UTC durante o envio pela rede … O JS do lado do cliente pode acessar facilmente de / para o UTC local. Veja: new Date().toJSON()

O JS a seguir manipulará a análise de uma data UTC / GMT no formato ISO8601 para um datetime local.

 if (typeof Date.fromISOString != 'function') { //method to handle conversion from an ISO-8601 style string to a Date object // Date.fromISOString("2009-07-03T16:09:45Z") // Fri Jul 03 2009 09:09:45 GMT-0700 Date.fromISOString = function(input) { var date = new Date(input); //EcmaScript5 includes ISO-8601 style parsing if (!isNaN(date)) return date; //early shorting of invalid input if (typeof input !== "string" || input.length < 10 || input.length > 40) return null; var iso8601Format = /^(\d{4})-(\d{2})-(\d{2})((([T ](\d{2}):(\d{2})(:(\d{2})(\.(\d{1,12}))?)?)?)?)?([Zz]|([-+])(\d{2})\:?(\d{2}))?$/; //normalize input var input = input.toString().replace(/^\s+/,'').replace(/\s+$/,''); if (!iso8601Format.test(input)) return null; //invalid format var d = input.match(iso8601Format); var offset = 0; date = new Date(+d[1], +d[2]-1, +d[3], +d[7] || 0, +d[8] || 0, +d[10] || 0, Math.round(+("0." + (d[12] || 0)) * 1000)); //use specified offset if (d[13] == 'Z') offset = 0-date.getTimezoneOffset(); else if (d[13]) offset = ((parseInt(d[15],10) * 60) + (parseInt(d[16],10)) * ((d[14] == '-') ? 1 : -1)) - date.getTimezoneOffset(); date.setTime(date.getTime() + (offset * 60000)); if (date.getTime() < = new Date(-62135571600000).getTime()) // CLR DateTime.MinValue return null; return date; }; } 

Aqui o código para converter uma zona DateTime para outra zona DateTime

  DECLARE @UTCDateTime DATETIME = GETUTCDATE(); DECLARE @ConvertedZoneDateTime DATETIME; -- 'UTC' to 'India Standard Time' DATETIME SET @ConvertedZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' SELECT @UTCDateTime AS UTCDATE,@ConvertedZoneDateTime AS IndiaStandardTime -- 'India Standard Time' to 'UTC' DATETIME SET @UTCDateTime = @ConvertedZoneDateTime AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC' SELECT @ConvertedZoneDateTime AS IndiaStandardTime,@UTCDateTime AS UTCDATE 

Nota : Isso ( AT TIME ZONE ) trabalhando apenas no SQL Server 2016+ e essa vantagem está considerando automaticamente a Luz do dia durante a conversão para o fuso horário específico

Sim, até certo ponto, conforme detalhado aqui .
A abordagem que usei (antes de 2008) é fazer a conversão na lógica de negócios do .NET antes de inserir no database.

Você pode usar a function GETUTCDATE () para obter datetime UTC Provavelmente, você pode selecionar a diferença entre GETUTCDATE () e GETDATE () e usar essa diferença para ajustar suas datas para UTC

Mas eu concordo com a mensagem anterior, que é muito mais fácil controlar o datetime certo na camada de negócios (no .NET, por exemplo).

Uso da amostra:

 SELECT Getdate=GETDATE() ,SysDateTimeOffset=SYSDATETIMEOFFSET() ,SWITCHOFFSET=SWITCHOFFSET(SYSDATETIMEOFFSET(),0) ,GetutcDate=GETUTCDATE() GO 

Retorna:

 Getdate SysDateTimeOffset SWITCHOFFSET GetutcDate 2013-12-06 15:54:55.373 2013-12-06 15:54:55.3765498 -08:00 2013-12-06 23:54:55.3765498 +00:00 2013-12-06 23:54:55.373