Converting GMT to EST in SQL Server-CodePudding

I have a table which stores records for Daylight saving and non-Daylight savings

CREATE TABLE #tmp 
(
    ID int,
    IntervalName nvarchar(100),
    StartDate datetime,
    EndDate Datetime,
    offset numeric(5, 2)
)

INSERT INTO #tmp 
VALUES (1, 'EDT', '2022-03-13 07:00:00.000', '2022-11-06 06:00:00.000',-4.00)
INSERT INTO #tmp 
VALUES (2, 'EST', '2022-11-06 06:00:00.000', '2023-03-12 07:00:00.000', -5.00)

I have my transactional tables which have Date column in the GMT timezone.

   ID   DatetimeGMT                  DatetimeLocal  
   ---------------------------------------------------------
   1    2022-11-05 07:00:00.000      2022-11-05 03:00:00.000        
   2    2022-11-10 06:00:00.000      2023-11-10 01:00:00.000

Now my DatetimeLocal column is calculating the offset hours based on the DatetimeGMT column.

The row with ID = 1 falls under offset -4 and the row with ID = 2 falls under offset -5.

Is there any suggestion how we can achieve this?

CodePudding user response：

If you have the offsets all stored in a table like that, then you just need to JOIN to find the correct one.

drop table if exists #tmp
drop table if exists #tran
go
CREATE TABLE #tmp 
(
    ID int,
    IntervalName nvarchar(100),
    StartDate datetime,
    EndDate Datetime,
    offset numeric(5, 2)
)

INSERT INTO #tmp 
VALUES (1, 'EDT', '2022-03-13 07:00:00.000', '2022-11-06 06:00:00.000',-4.00)
INSERT INTO #tmp 
VALUES (2, 'EST', '2022-11-06 06:00:00.000', '2023-03-12 07:00:00.000', -5.00)

create table #tran(id int primary key, DateTimeUTC datetime)
insert into #tran(id,DateTimeUTC) values (1,'2022-11-05 07:00:00.000'),(2,'2022-11-10 06:00:00.000')


select t.id, t.DateTimeUTC, dateadd(hour,offset,t.DateTimeUTC) DateTimeLocal, tz.offset
from #tran t
join #tmp tz
  on t.DateTimeUTC >= tz.StartDate
 and t.DateTimeUTC < tz.EndDate

outputs

id          DateTimeUTC             DateTimeLocal           offset
----------- ----------------------- ----------------------- ---------------------------------------
1           2022-11-05 07:00:00.000 2022-11-05 03:00:00.000 -4.00
2           2022-11-10 06:00:00.000 2022-11-10 01:00:00.000 -5.00

SQL Server 2016 and later have the offsets built-in, so you can write queries like

select t.id, 
       t.DateTimeUTC, 
       cast(t.DateTimeUTC at time zone 'UTC' at time zone 'EASTERN STANDARD TIME' as datetime) DateTimeLocal
from #tran t