Switching the offset for a UTC date in SQL Server

I was doing some work recently where I needed to group some data from SQL Server by year, month, week, etc.

The simplified version was something like this:

SELECT  
    COUNT(x),   
    YEAR(CreatedUtc)
FROM    
    dbo.[Order]
GROUP BY
    YEAR(CreatedUtc)

The CreatedUtc data type is datetimeoffset storing zulu datetimes.

Even though I knew I was only looking at data from 2020 I was still seeing 2 rows - one for 2019 and one for 2020.

I tested my date filtering (omitted for brevity in the sample above) and everything was fine. All dates was within the 2020 UTC bounds.

So after digging a bit more I found the offending row resulting in the 2019 group. The date was: ‘2019-12-31 23:56:43.0052080 +00:00’

Thinking of it, it of course makes perfect sense that the built-in YEAR function is not aware of timezones.

YEAR(‘2019-12-31 23:56:43.0052080 +00:00’) will (naturally) return 2019.

We need to switch the offset in the datetimeoffset to the timezone required for the task. In my use-case of a building a report that means switching to CEST/Central European Standard Time.

The YEAR snippet now instead becomes:

YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))

The full (still simplified) sample becomes:

`
SELECT  
    COUNT(x),   
    YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))
FROM
    dbo.[Order]
GROUP BY
    YEAR(SWITCHOFFSET(CreatedUtc, DATEPART(TZOFFSET, CreatedUtc AT TIME ZONE 'Central Europe Standard Time')))

Notice: I haven’t yet done any benchmarking on the above approach and it might make sense to extract this to a function to clean things up a bit.

Also see the official SWITCHOFFSET documentation.
https://docs.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15

If you have another better approach please do let me know :-)