Converting datetimeoffset to datetime with adjustment

Question

Hey folks:

I know this has to be simple - but I am not having fun dealing with it. I am in SQL Server 2019. My real problem is I am attempting to process a json file whose times are stored in UTC. So - when I select the time field, I do so with at time zone 'US Eastern Standard Time'. Sure enough - I get a datetimeoffset where the datetime is the UTC time in json - but I get the -5 adjustment. That's where I am coming from - but for simplicity consider the code below:

declare @o datetimeoffset = '2021-02-13 01:23:00.000 -5:00'
declare @p datetime;

set @p = convert(datetime, @o);
select @o, @p

set @p = convert(datetime, @o at time zone 'US Eastern Standard Time', 1);
select @o, @p

I want @p to equal 2021-12-12 20:23:00.000. That is the UTC time minus the 5 hour off set. If I do a straight convert - it simply strips the offset off. The time is still UTC. If I again do the "At timezone..." thing - it ADDS five hours - not subtracts them.

Please help. What am I doing wrong.

Answer 1

You are misunderstanding what the offset means.

The "-5:00" means the time "01:23:00.000" is UTC-5:00 hours already. It is already EST. UTC offset would be "-0:00".

Answer 2

Hey Tom:

Thanks. You get me 90% there. You are correct - the "at timezone" simply adds time zone information to a datetime. (The -5:00)
So - a better way to express my problem is I have a datetime that I know by business rule is in GMT. I want a datetime in EST.
In my case - my GMT date time is coming from a json file I want to load into my database. What I did (simplified a bit) is:

declare @Results table
(
StartDateGMT datetime,
EndDateGMT datetime,
StartDate datetime,
EndDate datetime,
Displacement int
)

insert into @Results
(
StartDateGMT,
EndDateGMT
)
values
('2021-03-12 03:14:00.000', '2021-03-12 11:03:00.000'),
('2021-03-13 01:08:00.000', '2021-03-13 10:32:00.000'),
('2021-03-14 02:26:00.000', '2021-03-14 11:12:00.000'),
('2021-03-15 01:27:00.000', '2021-03-15 08:52:00.000');

update @Results
set Displacement = datepart(tz, StartDateGMT at time zone 'Eastern Standard Time');

update @Results
set StartDate = dateadd(minute, Displacement, StartDateGMT),
EndDate = dateadd(minute, Displacement, EndDateGMT)

select * from @Results;

If you run this - I get the results I want. StartDate and EndDate are in EST. Note: I need to calculate a displacement for every row. Reason:
the displacement will be different depending on Daylight Savings Time. The top two rows in my example are Standard Time. The bottom two are daylight.
Notice the Displacement is different.

Thanks again.

Answer 3

The "at time zone" handles the DST correctly.

All you need to do is this:

declare @Results table  
(  
StartDateGMT datetime,  
EndDateGMT datetime,  
StartDate datetime,  
EndDate datetime,  
Displacement int  
)  
  
insert into @Results  
(  
StartDateGMT,  
EndDateGMT  
)  
values  
('2021-03-12 03:14:00.000', '2021-03-12 11:03:00.000'),  
('2021-03-13 01:08:00.000', '2021-03-13 10:32:00.000'),  
('2021-03-14 02:26:00.000', '2021-03-14 11:12:00.000'),  
('2021-03-15 01:27:00.000', '2021-03-15 08:52:00.000');  

SELECT
StartDateGMT,
StartDateGMT AT TIME ZONE 'UTC' at time zone 'Eastern Standard Time' as StartDateGMT_EST
FROM @Results
Order by StartDateGMT