CODEWITHSUNDEEP
sql-servert-sqltimezonesql-server-2008-r2utc
Raj More
Raj More

Reputation: 48024

TimeZone shifts using TSQL

I have incoming hourly data (spanning 5 years) with timestamps recorded in CST or EST. I want to store this in my database in UTC time.

Is there a way for me to convert from CST/CDT/CPT or EST/EDT/EPT to UTC using TSQL?

Upvotes: 1

Views: 1090

Answers (2)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56182

You can determine is datetime in row in EST or EDT:

Since 2007, the local time changes at 02:00 EST to 03:00 EDT on the second Sunday in March and returns at 02:00 EDT to 01:00 EST on the first Sunday in November, in the U.S. as well as in Canada.

Then apply DATEADD()

Mb my source code better explains what I mean:

declare @t table(dt datetime)

insert @t values ('2011-07-06T10:00:00'), ('2011-01-01T00:00:00'), ('2011-03-12T00:00:00'),
    ('2006-07-06T10:00:00')

select b.dt
    , CASE
        WHEN b.dt between b.[edt_start] and [edt_end] 
            THEN DATEADD(HH, -5, b.dt)
        ELSE DATEADD(HH, -4, b.dt)
    END

    , CASE
        WHEN b.dt between b.[edt_start] and [edt_end] 
            THEN '-05:00'
        ELSE '-04:00'
    END

from
(
    select a.dt
        , DATEADD(HH, 2, CASE 
            WHEN DATEPART(WEEKDAY, a.march) = 1 
                THEN a.march 
            ELSE DATEADD(DAY, 15 - DATEPART(WEEKDAY, a.march), a.march) 
        END) [edt_start]

        , DATEADD(HH, 2, CASE 
            WHEN DATEPART(WEEKDAY, a.november) = 1 
                THEN a.march 
            ELSE DATEADD(DAY, 8 - DATEPART(WEEKDAY, a.november), a.november) 
        END) [edt_end]
    from
    (
        select t.dt
            , YEAR(t.dt) [year]
            , CAST(CAST(YEAR(t.dt) as varchar(4)) + '03' + '01' as datetime) [march]
            , CAST(CAST(YEAR(t.dt) as varchar(4)) + '11' + '01' as datetime) [november]
        from @t t
    )a
)b

Upvotes: 1

David Steele
David Steele

Reputation: 3461

mI would recommend you store these as DATETIMEOFFSET to preserve the timezone information.

If you need to display then as UTC Dates then you can use SWITCHOFFSET

Upvotes: 2

Related Questions