How to convert from UTC to EST in SQL?-CodePudding

I am currently using:

sample_start_time AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS sample_start_time_est

from: Convert Datetime column from UTC to local time in select statement

sample_start_time
2021-03-10 21:13:00.000

becomes

sample_start_time_est
2021-03-10 16:13:00.000 -05:00

Though EST is now displayed, I think the system still recognizes it as UTC based on the results of subsequent queries. How do I get the system to recognize the adjusted time as EST?

For instance:

sample_start_time_est = 2021-03-10 16:14:00.000 -05:00
end_time = 2021-03-10 18:14:00.000
WHERE sample_start_time_est < end_time

above WHERE clause currently evaluates to FALSE but I want it to be TRUE

I am using Microsoft SQL Server Management Studio.

CodePudding user response：

The result you are seeing is happening because datetimeoffset has a higher precedence than datetime, and your comparison will force an implicit conversion. The implicit conversion will turn a datetime into a datetimeoffset with zero offset:

declare @dto_minus5 datetimeoffset = '2022-01-01 00:00:00 -05:00'
declare @later_dt datetime = '2022-01-01 01:00:00'; -- 1 hour later *if* we ignore the offset
declare @later_dt_casted datetimeoffset = cast(@later_dt as datetimeoffset);

select @dto_minus5, @later_dt_casted, iif(@dto_minus5 < @later_dt, 1, 0);

-- produces 2022-01-01 00:00:00 -05:00  2022-01-01 01:00:00  00:00  0

fiddle;

What happens in the iif is that @later_dt gets promoted to datetimeoffset, with an offset of 0. When these are then compared, the offset is taken into account. That is to say, the two values are "brought into the same time zone". That means my @dto_minus5 value gets 5 hours added to it (or equivalently, the @later_dt gets 5 hours subtracted).

That is to say, the comparison of:

2022-01-01 00:00:00 -05:00 vs 2022-01-01 01:00:00

becomes a comparison of:

2022-01-01 00:00:00 -05:00 vs 2022-01-01 01:00:00 00:00

Which in the same time zone is a comparison of:

2022-01-01 05:00:00 00:00 vs 2022-01-01 01:00:00 00:00

So the former is larger.

To get the semantics you want, you can use the todatetimeoffset() function:

declare @sample_start_time_est datetimeoffset = '2021-03-10 16:14:00.000 -05:00';
declare @end_time datetime = '2021-03-10 18:14:00.000';

select iif(@sample_start_time_est < todatetimeoffset(@end_time, '-05:00'), 1, 0);

-- prints 1

CodePudding user response：

CAST(sample_start_time AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME2) AS sample_start_time_est