CODEWITHSUNDEEP
sqlsql-server
spacerobot
spacerobot

Reputation: 297

SQL Convert Date with UTC Offset and Daylight Savings

Say I have the following timestamp: 2021-12-03 03:27:12

I know that this event actually occurred at 10:27PM Eastern Standard Time. I am trying to subtract the UTC offset from this db field and also adjust for daylight savings time. So the date above would subtract 5 hours. This timestamp would subtract 4: 2021-10-19 19:52. I have been messing around with many different SQL functions but have not got this to work.

Any suggestions would be appreciated.

Upvotes: 2

Views: 10554

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

AT TIME ZONE converts a DATETIME to a DATETIMEOFFSET at the specified timezone, or applies the correct offset (including daylight savings) to convert from a DATETIMEOFFSET in one time zone to another.

So

DECLARE @DATE DATETIME = '2021-12-03 03:27:12'
select @DATE 
select @DATE AT TIME ZONE 'UTC'
select @DATE AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time'

outputs

-----------------------
2021-12-03 03:27:12.000

----------------------------------
2021-12-03 03:27:12.000 +00:00

----------------------------------
2021-12-02 22:27:12.000 -05:00

So finally to convert a local UTC datetime to a local Eastern Standard datetime:

DECLARE @DATE DATETIME = '2021-12-03 03:27:12'
SELECT cast( @DATE AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time' as datetime );

Upvotes: 5

Dordi
Dordi

Reputation: 778

For UTC offset you can use this query

DECLARE @DATE DATETIME = '2021-12-03 03:27:12'

SELECT CONVERT(DATETIME, CONVERT(DATETIMEOFFSET, CONVERT(DATETIMEOFFSET
            -- assuming all servers are on CEST time
            , @DATE AT TIME ZONE 'Eastern Standard Time') AT TIME ZONE 'UTC'));

and for Daylight-savings you can use the function in this article

Upvotes: 0

Related Questions