Convert Historical Local Time to UTC Time in SQL Server

Question

I am confronting an SQL Server database which contains many DateTime values stored in local time. (Yes, this is unfortunate!) We have perhaps 5-10 years of data, which means that the UTC offset for this location will change depending on the time of year if the region in question observes Daylight Savings Time, and of course the schedule on which that change occurred may also change, as for example it did in the United States (where most of these data originate) back in 2007.

My objective is to convert these DateTimes to UTC time at the SQL level. Short of loading the entire Olson or TZ Database and querying it, does anyone have a technique for converting an historical local timestamp to a UTC time? [If it helps, conveniently, we happen to have the latitude and longitude for each row as well (could be used to identify timezone.]

Note: for a row written in real time, the trick of DATEDIFF(Hour, Getutcdate(), GETDATE()) AS UtcOffset works fine, of course. The problem is applying this retroactively to dates that occurred on either side of the Daylight Savings Time "barrier".

Answer 1

You can use AT TIME ZONE to convert to UTC. SQL knows about the switches to daylight savings so it will account for it. You just have to figure out the timezone (using the latitude and longitude, as you said).

You can get all timezones from here: SELECT * FROM sys.time_zone_info

So the solution will be something like this: First, add a column to your table with timezone (which you find out using the latitude and longitude). Then update your (newly added) UTC date column with AT TIME ZONE, for example:

-- some sample data to play with
CREATE TABLE #YourTable
(
    LocalDateTime DATETIME,
    [UtcDateTime] DATETIMEOFFSET,
    TimeZoneName VARCHAR(100)
);

INSERT INTO #YourTable
(
    LocalDateTime,
    TimeZoneName
)
VALUES
('20150101', 'Alaskan Standard Time'),
('20150101', 'US Mountain Standard Time'),
('20190701', 'Alaskan Standard Time'),
('20190701', 'US Mountain Standard Time');


-- convert to UTC
UPDATE #YourTable
SET [UtcDateTime] = LocalDateTime AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC';

-- check results
SELECT * FROM #YourTable;

Answer 2

This is based on a previous answer by Chris Barlow, at

SQL Server - Convert date field to UTC

This is a solution component in the form of a SQL Server 2008 view that includes a daylight savings (DST) rules approach for historical data conversion.

(No lat/long data needed.)

You can use this view to create your custom solution referencing for update, your local table columns that might need to be converted, like dbo.mytable.created_date.

Some notes on using the view are referenced below, of interest is the section "EXAMPLE USAGE - FOR HISTORICAL DATA CONVERSION":

--
--  DATETIME VS. DATETIMEOFFSET
--
--  WHERE,      t = '2016-12-13 04:32:00'
--
    declare 
            @Sydney DATETIME
    set 
            @Sydney                         = '2016-12-13 04:32:00'
    select  
            Sydney                          = @Sydney

    declare 
            @Sydney_UTC DATETIMEOFFSET
    set 
            @Sydney_UTC                     = '2016-12-13 04:32:00.6427663 +10:00'
    select  
            Sydney_UTC                      = @Sydney_UTC                   

    declare 
            @NewYork DATETIME
    set 
            @NewYork                        = '2016-12-13 04:32:00:34'
    select  
            NewYork                         = @NewYork

    declare 
            @NewYork_UTC DATETIMEOFFSET
    set 
            @NewYork_UTC                        = '2016-12-13 04:32:00.6427663 -04:00'
    select  
            NewYork_UTC                     = @NewYork_UTC                  

    select  
            DATEDIFF(hh, @Sydney, @NewYork) as DIFF_DATETIME

    select  
            DATEDIFF(hh, @Sydney_UTC, @NewYork_UTC) as DIFF_DATETIMEOFFSET

--
--  LOCAL UTC OFFSET FOR REAL-TIME DATA TODAY
--
    select  
            DATEDIFF( Hour, GETUTCDATE(), GETDATE() ) AS UtcOffset

--
--  LOCAL UTC DATE FOR REAL-TIME DATA TODAY - EASTERN STANDARD EXAMPLE
--
    select
            convert( datetimeoffset( 5 ), GETDATE(), 120 )

--
--  EXAMPLE USAGE -
--
    select
        *
    from
        vw_datetime__dst__timezone

--
--  EXAMPLE USAGE - FOR HISTORICAL DATA CONVERSION - EASTERN STANDARD
--
    select
        created_date,

        isnull( dst.zone, 'NO TZ' )             as zone,

        isnull( 
            case
                when                    created_date >= dstlow                          and
                                    created_date < dsthigh
                then                    dst.daylight
                else                    dst.standard
            end,
            'NO OFFSET'
        )                           as zone_offsettime,

            TODATETIMEOFFSET(
                created_date,
                case
                    when                created_date >= dstlow                          and
                                    created_date < dsthigh
                    then                dst.daylight
                    else                dst.standard
                end
            )                       as zone_time,

            SWITCHOFFSET(
                TODATETIMEOFFSET(
                    created_date,
                    case
                        when            created_date >= dstlow                          and
                                    created_date < dsthigh
                        then            dst.daylight
                        else            dst.standard
                    end
                ),

                '+00:00' -- parameterize?

            )                       as utc_time

    from
        (
            select GETDATE()                as created_date

        union
            select SYSDATETIMEOFFSET()          as created_date

        union
            select '2017-01-01 15:20:24.653'        as created_date

        ) DYNAMIC_temp_table

        left outer join vw_datetime__dst__timezone dst on
        created_date                        between yrstart and yrend                       and
        dst.zone                        = 'ET'
    order by
        created_date

-- Here is the view SQL:

drop view
    vw_datetime__dst__timezone
go
create view
    vw_datetime__dst__timezone
as
select
    yr,
    zone,
    standard,
    daylight,
    rulename,
    strule,
    edrule,
    yrstart,
    yrend,
    dateadd(day, (stdowref + stweekadd), stmonthref) dstlow,
    dateadd(day, (eddowref + edweekadd), edmonthref)  dsthigh
from (
    select
        yrs.yr,

        timezone.zone,
        timezone.standard,
        timezone.daylight,
        timezone.rulename,

        dst_rule.strule,
        dst_rule.edrule, 

        yrs.yr + '-01-01 00:00:00' yrstart,

        yrs.yr + '-12-31 23:59:59' yrend,

        yrs.yr + dst_rule.stdtpart + ' ' + dst_rule.cngtime stmonthref,

        yrs.yr + dst_rule.eddtpart + ' ' + dst_rule.cngtime edmonthref,

        case
            when                            dst_rule.strule in ('1', '2', '3')
            then
                case
                    when                    datepart(dw, yrs.yr + dst_rule.stdtpart) = '1'
                    then                    0
                    else                    8 - datepart(dw, yrs.yr + dst_rule.stdtpart)
                end
            else                            (datepart(dw, yrs.yr + dst_rule.stdtpart) - 1) * -1

        end                             as stdowref,

        case
            when                            dst_rule.edrule in ('1', '2', '3')
            then
                case
                    when                    datepart(dw, yrs.yr + dst_rule.eddtpart) = '1'
                    then                    0
                    else                    8 - datepart(dw, yrs.yr + dst_rule.eddtpart)
                end
            else                            (datepart(dw, yrs.yr + dst_rule.eddtpart) - 1) * -1

        end                             as eddowref,

        datename(dw, yrs.yr + dst_rule.stdtpart)                as stdow,
        datename(dw, yrs.yr + dst_rule.eddtpart)                as eddow,
        case
            when                            dst_rule.strule in ('1', '2', '3')
            then                            (7 * CAST(dst_rule.strule AS Integer)) - 7
            else                            0

        end                             as stweekadd,

        case
            when                            dst_rule.edrule in ('1', '2', '3')
            then                            (7 * CAST(dst_rule.edrule AS Integer)) - 7
            else                            0

        end                             as edweekadd

from (
    select          '1900' yr
        union select    '1901' yr
        union select    '1902' yr
        union select    '1903' yr
        union select    '1904' yr
        union select    '1905' yr
        union select    '1906' yr
        union select    '1907' yr
        union select    '1908' yr
        union select    '1909' yr
        union select    '1910' yr
        union select    '1911' yr
        union select    '1912' yr
        union select    '1913' yr
        union select    '1914' yr
        union select    '1915' yr
        union select    '1916' yr
        union select    '1917' yr
        union select    '1918' yr
        union select    '1919' yr
        union select    '1920' yr
        union select    '1921' yr
        union select    '1922' yr
        union select    '1923' yr
        union select    '1924' yr
        union select    '1925' yr
        union select    '1926' yr
        union select    '1927' yr
        union select    '1928' yr
        union select    '1929' yr
        union select    '1930' yr
        union select    '1931' yr
        union select    '1932' yr
        union select    '1933' yr
        union select    '1934' yr
        union select    '1935' yr
        union select    '1936' yr
        union select    '1937' yr
        union select    '1938' yr
        union select    '1939' yr
        union select    '1940' yr
        union select    '1941' yr
        union select    '1942' yr
        union select    '1943' yr
        union select    '1944' yr
        union select    '1945' yr
        union select    '1946' yr
        union select    '1947' yr
        union select    '1948' yr
        union select    '1949' yr
        union select    '1950' yr
        union select    '1951' yr
        union select    '1952' yr
        union select    '1953' yr
        union select    '1954' yr
        union select    '1955' yr
        union select    '1956' yr
        union select    '1957' yr
        union select    '1958' yr
        union select    '1959' yr
        union select    '1960' yr
        union select    '1961' yr
        union select    '1962' yr
        union select    '1963' yr
        union select    '1964' yr
        union select    '1965' yr
        union select    '1966' yr
        union select    '1967' yr
        union select    '1968' yr
        union select    '1969' yr
        union select    '1970' yr
        union select    '1971' yr
        union select    '1972' yr
        union select    '1973' yr
        union select    '1974' yr
        union select    '1975' yr
        union select    '1976' yr
        union select    '1977' yr
        union select    '1978' yr
        union select    '1979' yr
        union select    '1980' yr
        union select    '1981' yr
        union select    '1982' yr
        union select    '1983' yr
        union select    '1984' yr
        union select    '1985' yr
        union select    '1986' yr
        union select    '1987' yr
        union select    '1988' yr
        union select    '1989' yr
        union select    '1990' yr
        union select    '1991' yr
        union select    '1992' yr
        union select    '1993' yr
        union select    '1994' yr
        union select    '1995' yr
        union select    '1996' yr
        union select    '1997' yr
        union select    '1998' yr
        union select    '1999' yr
        union select    '2000' yr
        union select    '2001' yr
        union select    '2002' yr
        union select    '2003' yr
        union select    '2004' yr
        union select    '2005' yr
        union select    '2006' yr -- OLD US RULES
        union select    '2007' yr
        union select    '2008' yr
        union select    '2009' yr
        union select    '2010' yr
        union select    '2011' yr
        union select    '2012' yr
        union select    '2013' yr
        union select    '2014' yr
        union select    '2015' yr
        union select    '2016' yr
        union select    '2017' yr
        union select    '2018' yr
        union select    '2018' yr
        union select    '2020' yr
        union select    '2021' yr
        union select    '2022' yr
        union select    '2023' yr
        union select    '2024' yr
        union select    '2025' yr
        union select    '2026' yr
        union select    '2027' yr
        union select    '2028' yr
        union select    '2029' yr
        union select    '2030' yr
        union select    '2031' yr
        union select    '2032' yr
        union select    '2033' yr
        union select    '2034' yr
        union select    '2035' yr
        union select    '2036' yr
        union select    '2037' yr
        union select    '2038' yr
        union select    '2039' yr
        union select    '2040' yr
        union select    '2041' yr
        union select    '2042' yr
        union select    '2043' yr
        union select    '2044' yr
        union select    '2045' yr
        union select    '2046' yr
        union select    '2047' yr
        union select    '2048' yr
        union select    '2049' yr
        union select    '2050' yr
        union select    '2051' yr
        union select    '2052' yr
        union select    '2053' yr
        union select    '2054' yr
        union select    '2055' yr
        union select    '2056' yr
        union select    '2057' yr
        union select    '2058' yr
        union select    '2059' yr
        union select    '2060' yr
        union select    '2061' yr
        union select    '2062' yr
        union select    '2063' yr
        union select    '2064' yr
        union select    '2065' yr
        union select    '2066' yr
        union select    '2067' yr
        union select    '2068' yr
        union select    '2069' yr
        union select    '2070' yr
        union select    '2071' yr
        union select    '2072' yr
        union select    '2073' yr
        union select    '2074' yr
        union select    '2075' yr
        union select    '2076' yr
        union select    '2077' yr
        union select    '2078' yr
        union select    '2079' yr
        union select    '2080' yr
        union select    '2081' yr
        union select    '2082' yr
        union select    '2083' yr
        union select    '2084' yr
        union select    '2085' yr
        union select    '2086' yr
        union select    '2087' yr
        union select    '2088' yr
        union select    '2089' yr
        union select    '2090' yr
        union select    '2091' yr
        union select    '2092' yr
        union select    '2093' yr
        union select    '2094' yr
        union select    '2095' yr
        union select    '2096' yr
        union select    '2097' yr
        union select    '2098' yr
        union select    '2099' yr
) yrs
cross join (
--  Dynamic, hardcoded table of timezone-based, daylight savings time (DST) rules
--                                              -- TIMEZONE
    select      'UTC'   zone, '+00:00' standard, '+01:00' daylight, 'UTC' rulename  -- UTC - STAGING ONLY - this line is not accurate

    union select    'CET'   zone, '+01:00' standard, '+02:00' daylight, 'EU' rulename   -- Centeral Europe

    union select    'ET'    zone, '-05:00' standard, '-04:00' daylight, 'US' rulename   -- Eastern Time
    union select    'CT'    zone, '-06:00' standard, '-05:00' daylight, 'US' rulename   -- Central Time
    union select    'MT'    zone, '-07:00' standard, '-06:00' daylight, 'US' rulename   -- Mountain Time
    union select    'PT'    zone, '-08:00' standard, '-07:00' daylight, 'US' rulename   -- Pacific Time
) timezone
join (
--  Dynamic, hardcoded table of country-based, daylight savings time (DST) rules
    select      'UTC'   rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime

--  Country - Europe
    union select    'EU'    rulename, 'L' strule, '-03-31' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2099 lastyr, '01:00:00' cngtime

--  Country - US
    union select    'US'    rulename, '1' strule, '-04-01' stdtpart, 'L' edrule, '-10-31' eddtpart, 1900 firstyr, 2006 lastyr, '02:00:00' cngtime
    union select    'US'    rulename, '2' strule, '-03-01' stdtpart, '1' edrule, '-11-01' eddtpart, 2007 firstyr, 2099 lastyr, '02:00:00' cngtime
) dst_rule on
    dst_rule.rulename                   = timezone.rulename             and
    datepart( year, yrs.yr )                between firstyr and lastyr
) dst_dates

go

Answer 3

I used the following to convert from local Eastern time to UTC (hence the fixed values of 4 and 5 in the function). If you have pre-2007 values, then you would in fact need to modify the udf_IsInDST below to accomodate that as well.

CREATE FUNCTION [dbo].[udf_ConvertTimeLocalToUTC](@dt DATETIME)
RETURNS DATETIME
AS
BEGIN

    SET @dt = DATEADD(HOUR, CASE WHEN [dbo].udf_IsInDST(@dt) = 1 THEN 4 ELSE 5 END, @dt)
    RETURN @dt
END
GO


CREATE FUNCTION [dbo].[udf_IsInDST](@dt DATETIME)
RETURNS BIT
AS
BEGIN

    DECLARE @returnValue BIT = 0
    DECLARE @mm INT = DATEPART(MONTH, @dt)
    DECLARE @dd INT = DATEPART(DAY, @dt)
    DECLARE @dow INT = DATEPART(dw, @dt)   -- 1 = sun
    DECLARE @hr INT = DATEPART(HOUR, @dt)

    SET @returnValue = 
    CASE WHEN @mm > 3 AND @mm < 11 THEN 1
         WHEN @mm = 3 THEN
            CASE WHEN @dd < 8 THEN 0
                 WHEN @dd >= 8 AND @dd <= 14 THEN (CASE WHEN @dow = 1 THEN (CASE WHEN @hr >= 2 THEN 1 ELSE 0 END) ELSE (CASE WHEN @dd - @dow >= 7 THEN 1 ELSE 0 END) END)
                 ELSE 1
            END

         WHEN @mm = 11 THEN
            CASE WHEN @dd < 7 THEN (CASE WHEN @dow = 1 THEN (CASE WHEN @hr < 2 THEN 1 ELSE 0 END) ELSE (CASE WHEN @dow > @dd THEN 1 ELSE 0 END) END)
                 ELSE 0
            END

         ELSE 0
    END;

    RETURN @returnValue
END
GO

Answer 4

I've used 2 methods in the past. The first was to create a .Net CLR that takes a datetime and timezone and returns the UTC datetime value which was stored with the data. The second solution was only required to work for a limited number of time zones and involved creating a table consisting of time zone ID, date from, date to and the correct UTC offset for dates in the past and 20 years in the future. From there it is simple to join and apply the correct offset.