0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Mattpats
  • 414
  • 2
  • 9
  • 21
  • Are these columns `datetimeoffset`? – AlwaysLearning Aug 10 '22 at 21:52
  • the datatype is currently DATETIME. I need to change it to DATETIMEOFFSET? – Mattpats Aug 10 '22 at 22:03
  • You want a time in Zone A to be greater than a time in Zone UTC when Zone A's local time, converted to UTC, would be greater than Zone UTC's local time, correct? – Ross Bush Aug 10 '22 at 22:19
  • @RossBush I think that's correct. Basically, one approach would be to subtract 4 hours from times that are originally in UTC, but then this would neglect daylight savings. I want DST to be accounted for. – Mattpats Aug 10 '22 at 22:41
  • Since 2014 Time zone info has been implemented in Sql Server. The general way of doing this is to convert all dates to a UTC, using the TimeZoneInfo functions, which account for various windows of dst, then do your math. – Ross Bush Aug 11 '22 at 00:45

2 Answers2

1
CAST(sample_start_time AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME2) AS sample_start_time_est
Mattpats
  • 414
  • 2
  • 9
  • 21
0

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
allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • If I understand correctly, your solution will not take into account daylight savings since you are subtracting 5 hours. I have thousands of values and cannot manually adjust this to be 4 or 5 for all values. – Mattpats Aug 10 '22 at 23:07
  • 1
    @Mattpats It's not quite that I'm subtracting 5 hours, it's that I'm specifying what offset I want to apply. You can also just do to the `end_time` value what you did to the `sample_start_time`. Either way, what you *can't* do is just compare a datetime to a datetimeoffset and get the semantics you're after. But if the existing data is a datetime it is not specifying anything about daylight savings, and there's no way for SQL to "just know" whether any particular value should be interpreted as DST or not – allmhuran Aug 10 '22 at 23:12