caracteristicas - sql server descargar

Convierta la columna de fecha y hora de UTC a la hora local en la instrucción de selección

Question

Estoy haciendo algunas consultas de selección de SQL y me gustaría convertir mi columna de fecha y hora de UTC en hora local para que se muestre como hora local en los resultados de mi consulta. Tenga en cuenta que NO estoy buscando hacer esta conversión a través de código, sino más bien cuando estoy haciendo consultas SQL manuales y aleatorias en contra de mis bases de datos.

Answer 1

Aquí hay una versión que cuenta con ahorros de luz diurna, compensación UTC, y no está bloqueada en un año en particular.

---------------------------------------------------------------------------------------------------
--Name: udfToLocalTime.sql
--Purpose: To convert UTC to local US time accounting for DST
--Author: Patrick Slesicki
--Date: 3/25/2014
--Notes: Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well.
-- Good only for US States observing the Energy Policy Act of 2005.
-- Function doesn''t apply for years prior to 2007.
-- Function assumes that the 1st day of the week is Sunday.
--Tests:
-- SELECT dbo.udfToLocalTime(''2014-03-09 9:00'', DEFAULT)
-- SELECT dbo.udfToLocalTime(''2014-03-09 10:00'', DEFAULT)
-- SELECT dbo.udfToLocalTime(''2014-11-02 8:00'', DEFAULT)
-- SELECT dbo.udfToLocalTime(''2014-11-02 9:00'', DEFAULT)
---------------------------------------------------------------------------------------------------
ALTER FUNCTION udfToLocalTime
(
@UtcDateTime AS DATETIME
,@UtcOffset AS INT = -8 --PST
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@PstDateTime AS DATETIME
,@Year AS CHAR(4)
,@DstStart AS DATETIME
,@DstEnd AS DATETIME
,@Mar1 AS DATETIME
,@Nov1 AS DATETIME
,@MarTime AS TIME
,@NovTime AS TIME
,@Mar1Day AS INT
,@Nov1Day AS INT
,@MarDiff AS INT
,@NovDiff AS INT
SELECT
@Year = YEAR(@UtcDateTime)
,@MarTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset, ''1900-01-01 02:00''))
,@NovTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset - 1, ''1900-01-01 02:00''))
,@Mar1 = CONVERT(CHAR(16), @Year + ''-03-01 '' + CONVERT(CHAR(5), @MarTime), 126)
,@Nov1 = CONVERT(CHAR(16), @Year + ''-11-01 '' + CONVERT(CHAR(5), @NovTime), 126)
,@Mar1Day = DATEPART(WEEKDAY, @Mar1)
,@Nov1Day = DATEPART(WEEKDAY, @Nov1)
--Get number of days between Mar 1 and DST start date
IF @Mar1Day = 1 SET @MarDiff = 7
ELSE SET @MarDiff = 15 - @Mar1Day
--Get number of days between Nov 1 and DST end date
IF @Nov1Day = 1 SET @NovDiff = 0
ELSE SET @NovDiff = 8 - @Nov1Day
--Get DST start and end dates
SELECT
@DstStart = DATEADD(DAY, @MarDiff, @Mar1)
,@DstEnd = DATEADD(DAY, @NovDiff, @Nov1)
--Change UTC offset if @UtcDateTime is in DST Range
IF @UtcDateTime >= @DstStart AND @UtcDateTime < @DstEnd SET @UtcOffset = @UtcOffset + 1
--Get Conversion
SET @PstDateTime = DATEADD(HOUR, @UtcOffset, @UtcDateTime)
RETURN @PstDateTime
END
GO

Answer 2

Aquí hay uno más simple que toma en cuenta

CREATE FUNCTION [dbo].[UtcToLocal]
(
@p_utcDatetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END

Answer 3

Como advertencia, si va a utilizar lo siguiente (tenga en cuenta los milisegundos en lugar de minutos):

 SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable

Tenga en cuenta que la parte DATEDIFF no siempre devolverá el mismo número. Entonces no lo use para comparar DateTimes a milisegundos.

Answer 4

Descubrí que esta función es más rápida que otras soluciones que usan una tabla o bucles separados. Es solo una declaración de caso básica. Dado que todos los meses entre abril y octubre tienen una compensación de -4 horas (hora del este), solo necesitamos agregar algunas líneas más para los días marginales. De lo contrario, el desplazamiento es -5 horas.

Esto es específico para una conversión de UTC a hora del este, pero se pueden agregar funciones de zona horaria adicionales según sea necesario.

USE [YourDatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[ConvertUTCtoEastern] Script Date: 11/2/2016 5:21:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ConvertUTCtoEastern]
(
@dtStartDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Working DATETIME
DECLARE @Returned DATETIME
SET @Working = @dtStartDate
SET @Working =
case when month(@Working) between 4 and 10 then dateadd(HH,-4,@Working)
when @Working between ''2017-03-12'' and ''2017-11-05'' then dateadd(HH,-4,@Working)
when @Working between ''2016-03-13'' and ''2016-11-06'' then dateadd(HH,-4,@Working)
when @Working between ''2015-03-08'' and ''2015-11-01'' then dateadd(HH,-4,@Working)
when @Working between ''2014-03-09'' and ''2014-11-02'' then dateadd(HH,-4,@Working)
when @Working between ''2013-03-10'' and ''2013-11-03'' then dateadd(HH,-4,@Working)
when @Working between ''2012-03-11'' and ''2012-11-04'' then dateadd(HH,-4,@Working)
else dateadd(HH,-5,@Working) end
SET @Returned = @Working
RETURN @Returned
END
GO

Answer 5

Encontré que el modo de función inactivo es demasiado lento cuando hay una gran cantidad de datos. Así que lo hice uniéndome a una función de tabla que permitiría un cálculo de la diferencia horaria. Básicamente se trata de segmentos de fecha y hora con el desplazamiento de la hora. Un año sería 4 filas. Entonces la función de tabla

dbo.fn_getTimeZoneOffsets(''3/1/2007 7:00am'', ''11/5/2007 9:00am'', ''EPT'')

Devolvería esta tabla:

startTime endTime offset isHr2
3/1/07 7:00 3/11/07 6:59 -5 0
3/11/07 7:00 11/4/07 6:59 -4 0
11/4/07 7:00 11/4/07 7:59 -5 1
11/4/07 8:00 11/5/07 9:00 -5 0

No cuenta para el horario de verano. Una muestra de cómo se usa está debajo y la publicación completa del blog está here .

select mt.startTime as startUTC,
dateadd(hh, tzStart.offset, mt.startTime) as startLocal,
tzStart.isHr2
from MyTable mt
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone) tzStart
on mt.startTime between tzStart.startTime and tzStart.endTime

Answer 6

Esto debería poder obtener el tiempo del servidor con DST

declare @dt datetime
set @dt = getutcdate() -- GMT equivalent

sysdatetimeoffset tiene DST en cuenta

select [InputTime] = @dt
, [LocalTime2] = dateadd(mi, datediff(mi, sysdatetimeoffset(),getdate()), @dt)

Answer 7

La respuesta de Ron contiene un error. Utiliza las 2:00 a.m. hora local donde se requiere el equivalente UTC. No tengo suficientes puntos de reputación para comentar la respuesta de Ron, por lo que aparece una versión corregida a continuación:

-- =============================================
-- Author: Ron Smith
-- Create date: 2013-10-23
-- Description: Converts UTC to DST
-- based on passed Standard offset
-- =============================================
CREATE FUNCTION [dbo].[fn_UTC_to_DST]
(
@UTC datetime,
@StandardOffset int
)
RETURNS datetime
AS
BEGIN
declare
@DST datetime,
@SSM datetime, -- Second Sunday in March
@FSN datetime -- First Sunday in November
-- get DST Range
set @SSM = datename(year,@UTC) + ''0314''
set @SSM = dateadd(hour,2 - @StandardOffset,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@UTC) + ''1107''
set @FSN = dateadd(second,-1,dateadd(hour,2 - (@StandardOffset + 1),dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))
-- add an hour to @StandardOffset if @UTC is in DST range
if @UTC between @SSM and @FSN
set @StandardOffset = @StandardOffset + 1
-- convert to DST
set @DST = dateadd(hour,@StandardOffset,@UTC)
-- return converted datetime
return @DST
END

Answer 8

Ninguno de estos funcionó para mí, pero esto a continuación funcionó al 100%. Espero que esto ayude a otros a tratar de convertirlo como yo.

CREATE FUNCTION [dbo].[fn_UTC_to_EST]
(
@UTC datetime,
@StandardOffset int
)
RETURNS datetime
AS
BEGIN
declare
@DST datetime,
@SSM datetime, -- Second Sunday in March
@FSN datetime -- First Sunday in November
-- get DST Range
set @SSM = DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))+''02:00:00''
set @FSN = DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0)) +''02:00:00''
-- add an hour to @StandardOffset if @UTC is in DST range
if @UTC between @SSM and @FSN
set @StandardOffset = @StandardOffset + 1
-- convert to DST
set @DST = dateadd(hour,@StandardOffset,@UTC)
-- return converted datetime
return @DST
END

Answer 9

No encontré ninguno de estos ejemplos útil para obtener una fecha y hora almacenada como UTC en una fecha y hora en una zona horaria específica (NO la zona horaria del servidor porque las bases de datos SQL de Azure se ejecutan como UTC). Así es como lo manejé. No es elegante, pero es simple y te da la respuesta correcta sin mantener otras tablas:

select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET,
dateTimeField AT TIME ZONE ''Eastern Standard Time'')))

Answer 10

No hay una manera simple de hacer esto de una manera correcta Y genérica.

En primer lugar, debe entenderse que la compensación depende de la fecha en cuestión, la zona horaria y el horario de verano. GetDate()-GetUTCDate solo le da el desplazamiento hoy en el TZ del servidor, que no es relevante.

Solo he visto dos soluciones de trabajo y busco mucho.

1) Una función SQL personalizada con un par de tablas de datos base, tales como Zonas horarias y reglas DST por TZ. Trabajando pero no muy elegante. No puedo publicarlo ya que no soy el propietario del código.

EDITAR: Aquí hay un ejemplo de este método https://gist.github.com/drumsta/16b79cee6bc195cd89c8

2) Agregue un ensamblado .net al db, .Net puede hacer esto muy fácilmente. Esto funciona muy bien, pero la desventaja es que debe configurar varios parámetros en el nivel del servidor y la configuración se rompe fácilmente, por ejemplo, si restaura la base de datos. Utilizo este método pero no puedo publicarlo ya que no poseo el código.

Answer 11

Primera función: configurada para la zona horaria italiana (+1, +2), cambie las fechas: último domingo de marzo y octubre, devuelva la diferencia entre la zona horaria actual y la fecha y hora como parámetro.

Returns:
current timezone < parameter timezone ==> +1
current timezone > parameter timezone ==> -1
else 0

El código es:

CREATE FUNCTION [dbo].[UF_ADJUST_OFFSET]
(
@dt_utc datetime2(7)
)
RETURNS INT
AS
BEGIN
declare @month int,
@year int,
@current_offset int,
@offset_since int,
@offset int,
@yearmonth varchar(8),
@changeoffsetdate datetime2(7)
declare @lastweek table(giorno datetime2(7))
select @current_offset = DATEDIFF(hh, GETUTCDATE(), GETDATE())
select @month = datepart(month, @dt_utc)
if @month < 3 or @month > 10 Begin Set @offset_since = 1 Goto JMP End
if @month > 3 and @month < 10 Begin Set @offset_since = 2 Goto JMP End
--If i''m here is march or october
select @year = datepart(yyyy, @dt_utc)
if @month = 3
Begin
Set @yearmonth = cast(@year as varchar) + ''-03-''
Insert Into @lastweek Values(@yearmonth + ''31 03:00:00.000000''),(@yearmonth + ''30 03:00:00.000000''),(@yearmonth + ''29 03:00:00.000000''),(@yearmonth + ''28 03:00:00.000000''),
(@yearmonth + ''27 03:00:00.000000''),(@yearmonth + ''26 03:00:00.000000''),(@yearmonth + ''25 03:00:00.000000'')
--Last week of march
Select @changeoffsetdate = giorno From @lastweek Where datepart(weekday, giorno) = 1
if @dt_utc < @changeoffsetdate
Begin
Set @offset_since = 1
End Else Begin
Set @offset_since = 2
End
End
if @month = 10
Begin
Set @yearmonth = cast(@year as varchar) + ''-10-''
Insert Into @lastweek Values(@yearmonth + ''31 03:00:00.000000''),(@yearmonth + ''30 03:00:00.000000''),(@yearmonth + ''29 03:00:00.000000''),(@yearmonth + ''28 03:00:00.000000''),
(@yearmonth + ''27 03:00:00.000000''),(@yearmonth + ''26 03:00:00.000000''),(@yearmonth + ''25 03:00:00.000000'')
--Last week of october
Select @changeoffsetdate = giorno From @lastweek Where datepart(weekday, giorno) = 1
if @dt_utc > @changeoffsetdate
Begin
Set @offset_since = 1
End Else Begin
Set @offset_since = 2
End
End
JMP:
if @current_offset < @offset_since Begin
Set @offset = 1
End Else if @current_offset > @offset_since Set @offset = -1 Else Set @offset = 0
Return @offset
END

Entonces la función que convierte la fecha

CREATE FUNCTION [dbo].[UF_CONVERT]
(
@dt_utc datetime2(7)
)
RETURNS datetime
AS
BEGIN
declare @offset int
Select @offset = dbo.UF_ADJUST_OFFSET(@dt_utc)
if @dt_utc >= ''9999-12-31 22:59:59.9999999''
set @dt_utc = ''9999-12-31 23:59:59.9999999''
Else
set @dt_utc = (SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), @dt_utc) )
if @offset <> 0
Set @dt_utc = dateadd(hh, @offset, @dt_utc)
RETURN @dt_utc
END

Answer 12

Puede hacerlo de la siguiente manera en SQL Server 2008 o superior:

SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
MyTable.UtcColumn),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS ColumnInLocalTime
FROM MyTable

También puede hacer lo menos detallado:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable

Hagas lo que hagas, no lo uses - para restar fechas, porque la operación no es atómica y ocasionalmente obtendrás resultados indeterminados debido a las condiciones de carrera entre el sistema datetime y el horario local verificado en diferentes momentos (es decir, no atómicamente) )

Tenga en cuenta que esta respuesta no tiene en cuenta el horario de verano. Si desea incluir un ajuste de horario de verano, consulte también la siguiente pregunta de SO:

Cómo crear la función de inicio y finalización del horario de verano en SQL Server

Answer 13

Si habilitar CLR en su base de datos es una opción así como también usar la zona horaria del servidor sql, se puede escribir en .Net con bastante facilidad.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime fn_GetLocalFromUTC(SqlDateTime UTC)
{
if (UTC.IsNull)
return UTC;
return new SqlDateTime(UTC.Value.ToLocalTime());
}
}

Un valor de fecha y hora UTC entra y sale el valor de fecha y hora local relativo al servidor. Los valores nulos devuelven nulo.

Answer 14

Si necesita una conversión que no sea la ubicación de su servidor, aquí hay una función que le permite pasar un desplazamiento estándar y cuentas para los Horarios de verano de los Estados Unidos:

-- =============================================
-- Author: Ron Smith
-- Create date: 2013-10-23
-- Description: Converts UTC to DST
-- based on passed Standard offset
-- =============================================
CREATE FUNCTION [dbo].[fn_UTC_to_DST]
(
@UTC datetime,
@StandardOffset int
)
RETURNS datetime
AS
BEGIN
declare
@DST datetime,
@SSM datetime, -- Second Sunday in March
@FSN datetime -- First Sunday in November
-- get DST Range
set @SSM = datename(year,@UTC) + ''0314''
set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@UTC) + ''1107''
set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))
-- add an hour to @StandardOffset if @UTC is in DST range
if @UTC between @SSM and @FSN
set @StandardOffset = @StandardOffset + 1
-- convert to DST
set @DST = dateadd(hour,@StandardOffset,@UTC)
-- return converted datetime
return @DST
END
GO

Answer 15

Usando nuevas oportunidades de SQL Server 2016:

CREATE FUNCTION ToLocalTime(@dtUtc datetime, @timezoneId nvarchar(256))
RETURNS datetime
AS BEGIN
return @dtUtc AT TIME ZONE ''UTC'' AT TIME ZONE @timezoneId
/* -- second way, faster
return SWITCHOFFSET(@dtUtc , DATENAME(tz, @dtUtc AT TIME ZONE @timezoneId))
*/
/* -- third way
declare @dtLocal datetimeoffset
set @dtLocal = @dtUtc AT TIME ZONE @timezoneId
return dateadd(minute, DATEPART (TZoffset, @dtLocal), @dtUtc)
*/
END
GO

Pero el procedimiento clr funciona 5 veces más rápido: ''- (

Preste atención a que Offset para una TimeZone puede cambiar a invierno o verano. Por ejemplo

select cast(''2017-02-08 09:00:00.000'' as datetime) AT TIME ZONE ''Eastern Standard Time''
select cast(''2017-08-08 09:00:00.000'' as datetime) AT TIME ZONE ''Eastern Standard Time''

resultados:

2017-02-08 09:00:00.000 -05:00
2017-08-08 09:00:00.000 -04:00

No puedes simplemente agregar compensación constante.

Answer 16

 declare @mydate2 datetime
set @mydate2=Getdate()
select @mydate2 as mydate,
dateadd(minute, datediff(minute,getdate(),@mydate2),getutcdate())