SQL Convert Date with UTC Offset and Daylight Savings-CodePudding

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.

CodePudding user response：

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.

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

CodePudding user response：

for a fixed -4 hours

SELECT DATEADD (hour, -4, '2021-12-03 03:27:12')

https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15

CodePudding user response：

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