February 21, 2022 at 8:59 pm
Hello - The following two queries generate different results depending on whether the DateTimeOffset field is converted to Datetime. Basically, I'm trying to assess whether a given DateTimeOffset value occurs prior or after a specified date.
/*
Queries tested on SQL Server 2012 with default timezone of Eastern Standard Time.
*/
Declare
@ComparisonDateAsDTO datetimeoffset(7) = '2022-02-17 20:22:47.8048644 -05:00'
,@AnchorDate datetime
set @AnchorDate = '2022-02-18'
/* Test1: Compare DateTimeOffset value to DateTime value. */
select
ComparisonDateAsDTO = @ComparisonDateAsDTO
,AnchorDate = @AnchorDate
,IsComparisonDateMoreRecentThanAnchorDate = case when @ComparisonDateAsDTO>=@AnchorDate then 'Yes' else 'No' end
/* Test2: DateTimeOffset value converted to DateTime prior to comparison. */
Select
ComparisonDateAsDateTime = convert(datetime, @ComparisonDateAsDTO)
,AnchorDate = @AnchorDate
,IsConvertedComparisonDateMoreRecentThanAnchorDate = case when convert(datetime,@ComparisonDateAsDTO)>=@AnchorDate then 'Yes' else 'No' end
In the first query "Test 1", the DateTimeOffset value is being compared directly to a DateTime value. The result of Test 1 doesn't make sense to me given the offset of "-05:00" which equates to EST, and the server is also EST, which in my mind would yield the DateTimeOffset value as being older than the DateTime value since both values are contextualized within an EST timezone.
So, here are my questions:
2. Is the result of Test 2 actually the correct answer given that the server is in EST and the offset of the ComparisonDate also contextualizes its value within EST?
February 21, 2022 at 10:26 pm
Unless you explicitly convert the datetimeoffset to the correct UTC time -- e.g., for SQL 2012 --
SWITCHOFFSET(@ComparisonDateAsDTO,'+00:00')
-- datediff or less than/greater than comparisons effectively ignore/strip off the offset and just compare both values as datetimes.
February 21, 2022 at 10:27 pm
In reality you should use ToDateTimeOffset() to convert the date time then do the comparison, look at the values you get in this query. You need to tell sql server what offset your system is in.
Declare
@ComparisonDateAsDTO datetimeoffset(7) = '2022-02-17 20:22:47.8048644 -05:00'
,@AnchorDate datetime
set @AnchorDate = '2022-02-18'
select
cast(@AnchorDate as datetimeoffset(7)) as datetimecastAsDTO
,convert(datetime,@ComparisonDateAsDTO)
,TODATETIMEOFFSET(@AnchorDate, '-05:00')
February 21, 2022 at 10:31 pm
When you let it do the implicit conversion of anchor date it's setting the it to UTC time zone not converting, 2022-02-18 00:00:00.0000000 +00:00.
Which is actually less than 2022-02-17 20:22:47.8048644 -05:00.
February 22, 2022 at 9:12 am
You can also use the AT TIME ZONE functionality for the test
/*
Queries tested on SQL Server 2012 with default timezone of Eastern Standard Time.
*/
DECLARE @ComparisonDateAsDTO datetimeoffset(7) = '2022-02-17 20:22:47.8048644 -05:00'
, @AnchorDate datetime = '2022-02-18';
/*
Test3: AnchorDate and DateTimeOffset AT TIME ZONE "UTC".
*/
SELECT ComparisonDateAtUTC = @ComparisonDateAsDTO AT TIME ZONE('UTC')
, AnchorDateAtUTC = @AnchorDate AT TIME ZONE('UTC') -- Since there is no offset, SQL Assumes that this is already UTC
, IsComparisonDateAtUTC_MoreRecentThan_AnchorDateAtUTC = CASE WHEN @ComparisonDateAsDTO AT TIME ZONE('UTC') >= @AnchorDate AT TIME ZONE('UTC')
THEN 'Yes'
ELSE 'No'
END;
February 22, 2022 at 2:24 pm
I appreciate everyone's replies. Plenty of food for thought. Quick comment about AT TIME ZONE , not available in SQL 2012 unfortunately.
So, here's a follow-up question/concern. It's my understanding that the offset value provides the time zone (i.e. displacement from UTC) of the associated date & time, and that the date & time is not specifically UTC.
Example: for the following two datetimeoffset values, both are reflecting 5am in their respective offsets (time zones), right?
'2022-02-17 00:05:00.0000000 +00:00' --> i.e. 5am in UTC time zone
'2022-02-17 00:05:00.0000000 -05:00' --> i.e. 5am in eastern time zone
If my understanding is correct, then it would be incorrect to convert both DateTimeOffset values to DateTime and then compare these values since the explicit Datetime conversion would strip off the offset, implying that the values were equal when they are not.
Thoughts?
February 22, 2022 at 2:54 pm
Datetime/Datetime2 are not specifically anything, just are a date and a time. And yes as you saw with your issue converting from a datetime to a datetimeoffset does not preserve any local time zone, nor does switching from a datetimeoffset to a datetime convert the timezone as well.
Yes if you were going to convert to datetime before doing the comparison you would want to switch to the same timezone first. If you compare datetimeoffset directly you need to be aware of how those will compare as that will take into account the timezone.
And if you think dealing with timezones in SQL is weird have fun with it in something like java.
February 22, 2022 at 3:14 pm
Ah, I missed the fact that you are using SQL 2012.
The other option is to just force the AnchorDate to DTO (knowing that SQL assumes that it is UTC)
/*
Test4: AnchorDate converted to DTO
*/
SELECT ComparisonDate_AsDTO = @ComparisonDateAsDTO
, AnchorDateAtUTC_AsDTO = CONVERT(DATETIMEOFFSET(7), @AnchorDate) -- Since there is no offset, SQL Assumes that this is already UTC
, IsComparisonDateAsDTO_MoreRecentThan_AnchorDateAtUTCAsDTO = CASE WHEN @ComparisonDateAsDTO >= CONVERT(DATETIMEOFFSET(7), @AnchorDate)
THEN 'Yes'
ELSE 'No'
END;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply