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.