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 开发者_JAVA百科UTC time.

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

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

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