CODEWITHSUNDEEP
sql-servertimezone
Neha
Neha

Reputation: 163

SQL Server - convert UTC to EST (include Daylight Savings, when appropriate)

I have a table that stores the transaction date time in UTC time zone. I would need to convert this time to eastern time zone i.e. EST or EDT, depending on the transaction date.

How can I do this without writing a big function or creating a table / view that flags EST / EDT for each transaction date?

Upvotes: 0

Views: 2601

Answers (1)

Thom A
Thom A

Reputation: 95830

Use AT TIME ZONE, which changes the timezone of a value to the relevant time there, and observes Daylight Saving:

SELECT CONVERT(datetimeoffset(0),'2021-09-07T15:25:37+00:00') AT TIME ZONE 'Eastern Standard Time' AS Sep21,
       CONVERT(datetimeoffset(0),'2021-01-09T15:25:37+00:00') AT TIME ZONE 'Eastern Standard Time' AS Jan21;

Which returns the following:

Sep21                              Jan21
---------------------------------- ----------------------------------
2021-09-07 11:25:37 -04:00         2021-01-09 10:25:37 -05:00

Upvotes: 4

Related Questions