SQLTeam.com | Weblogs | Forums

Convert existing gmt datetime to Eastern time

I would like convert GMT to Eastern time on existing sql column factor in daylight savings time

Daylight savings started nov1 4 hr difference

2020-11-01 02:00.00 gmt
Should be 2020-10:31 10:00.00. Eastern

Prior to nov 1 it was 5 hr difference

2020-09-02 02:00.00 gmt
Should be 2020-09-01 09:00.00. Eastern

Declare @testDates Table (MyDate datetime);
 Insert Into @testDates (MyDate)
 Values ('2020-11-01 01:00:00.000')
      , ('2020-11-01 02:00:00.000')
      , ('2020-11-01 03:00:00.000')
      , ('2020-11-01 04:00:00.000')
      , ('2020-11-01 05:00:00.000')
      , ('2020-11-01 06:00:00.000')
      , ('2020-11-01 07:00:00.000')
      , ('2020-11-02 01:00:00.000')
      , ('2020-11-02 02:00:00.000')
      , ('2020-09-02 01:00:00.000')
      , ('2020-09-02 02:00:00.000');

 Select *
      , est = td.MyDate at time zone 'UTC' at time zone 'Eastern Standard Time'
   From @testDates              td;

The offset is -05:00 after 1 November and -04:00 prior to 1 November for 'Eastern Standard Time'.

You won't see the time change in GMT/UTC until 06:00:00.000 when converting to Eastern Standard Time. As you see in the example the time 05:00:00.000 has the offset of -04:00 and 06:00:00.000 has the offset of -05:00 and both equate to 01:00:00.000 Eastern.

FYI - you would be much better off storing the dates as a datetimeoffset instead of datetime/datetime2 as that will store the value in UTC with the offset and will be much easier to manage.