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())