DateTimeOffset

This week I found a SQL Statement with a not-so-nice WHERE clause:

    WHERE Year(t.StartDate) = 2024

It was reading the entire table, not just the rows from January 1st until December 31st.
I tried to fix it with

    WHERE t.StartDate >= ‘20240101’
    AND t.StartDate < ‘20250101’

But then I got incorrect results... (Boy, am I glad that I checked...)

Because t.StartDate was a DATETIMEOFFSET(7), and '20240101' just a DATETIME. And you should not compare dates across datatypes, because of "rounding". A date like "2024-01-01 00:00:00.0000000 +01:00" belongs to 2023

So I had to write:

    WHERE t.StartDate >= ‘2024-01-01 00:00:00.0000000 +01:00’
    AND t.StartDate < ‘2025-01-01 00:00:00.0000000 +01:00’

which is a bit long winded, or I could write

    WHERE t.StartDate >= CAST(‘20240101’ as datetime2) AT TIME ZONE ‘Central European Standard Time’
    AND t.StartDate < CAST(‘20250101’ as datetime2) AT TIME ZONE ‘Central European Standard Time’

even longer, but easier to read, I think.