How to fix a SQL table order-by DateTimeOffset with the offset sometimes missing

Question

I have a messy table that was filled with events that sometimes got a DateTime, and in other places got a DateTimeOffset assigned to the Date field, similar to this (provided you live far enough outside the Divided Kingdom's longitude):

DECLARE @MY_LOG TABLE ([MOMENT] DATETIMEOFFSET, [PAYLOAD] NVARCHAR(200));

INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (GETDATE(),'first entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (SYSDATETIMEOFFSET(),'second entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (GETDATE(),'third entry')
WAITFOR DELAY '00:00:00.100';
INSERT INTO @MY_LOG ([MOMENT],[PAYLOAD]) VALUES (SYSDATETIMEOFFSET(),'forth entry')

SELECT [MOMENT],[PAYLOAD] FROM @MY_LOG ORDER BY [MOMENT] ASC;

The result of the last select would be this example:

2020-03-31 02:39:10.6779279 +02:00  second entry
2020-03-31 02:39:10.8809259 +02:00  forth entry
2020-03-31 02:39:10.5730000 +00:00  first entry
2020-03-31 02:39:10.7770000 +00:00  third entry

I tried to fix it by using SWITCHOFFSET, which adds the missing offset, but also compensates for it:

SELECT [MOMENT],
       [PAYLOAD],
       CASE WHEN DATEPART(tz,[MOMENT])=0 THEN SWITCHOFFSET(MOMENT, '+02:00') ELSE MOMENT END AS FIXED 
FROM @MY_LOG ORDER BY FIXED ASC;

The result is:

2020-03-31 02:39:10.6779279 +02:00  second entry 2020-03-31 02:39:10.6779279 +02:00
2020-03-31 02:39:10.8809259 +02:00  forth entry  2020-03-31 02:39:10.8809259 +02:00
2020-03-31 02:39:10.5730000 +00:00  first entry  2020-03-31 04:39:10.5730000 +02:00
2020-03-31 02:39:10.7770000 +00:00  third entry  2020-03-31 04:39:10.7770000 +02:00

I may also need to account for switching moments like in This question, but for now I'm looking for a non destructive (read-only) solution to order the rows by date

score 0 · Accepted Answer · answered Mar 31 '20 at 01:10

Found the answer just before posting: strip the offset by converting to datetime2(7):

SELECT [MOMENT],
       [PAYLOAD],
       CONVERT(datetime2(7),[MOMENT]) AS FIXED
FROM @MY_LOG ORDER BY FIXED ASC;

result is a correctly ordered table (except for two hours per year when daylight savings switches).

2020-03-31 03:04:04.3630000 +00:00  first entry  2020-03-31 03:04:04.3630000
2020-03-31 03:04:04.4689375 +02:00  second entry 2020-03-31 03:04:04.4689375
2020-03-31 03:04:04.5670000 +00:00  third entry  2020-03-31 03:04:04.5670000
2020-03-31 03:04:04.6709404 +02:00  forth entry  2020-03-31 03:04:04.6709404

How to fix a SQL table order-by DateTimeOffset with the offset sometimes missing

1 Answers1