Group by month taking into account on timezone (SQL Server)

Question

I've seen the following question about grouping by month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0)

How to group by month from Date field using sql

This works - but I want to run this against a specific timezone, taking DST into account.

To be specific, the Netherlands timezone: Central European Standard Time (GMT+1).

This will be GMT+2 sometimes with DST.

In my case I have an Orders table with an OrderDatetime which is a DateTimeOffset(7). all values are UTC (+00:00).

SELECT
    DATEADD(MONTH, DATEDIFF(MONTH, 0, [Orders].[OrderDateTime]), 0) AS [Month],
    ...
FROM 
    [Orders]
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, [Orders].[OrderDateTime]), 0)

What would such query look like when taking into account a specific timezone?

Bonus: I'd preferably make this configurable, so the timezone would be a parameter - what would such query look like?

Answer 1

You can use SWITCHOFFSET

CREATE TABLE test   
(  
   OrderDateTime datetimeoffset 
,  tz varchar (8)
);  

INSERT INTO test   
VALUES ('2022-01-31 17:00:00 -5:00', '+08:00');  
 
SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, [OrderDateTime]), 0) AS [Month1]
  , DATEADD(MONTH, DATEDIFF(MONTH, 0, SWITCHOFFSET (OrderDateTime, tz)), 0) AS [Month2]
  
FROM test; 

Returns

Month1  Month2
2022-01-01 00:00:00.000 2022-02-01 00:00:00.000

Answer 2

If the value is already a datetimeoffset then SQL Server already takes timezones into account, so I suspect the problem you think exists, doesn't.

Take the following:

CREATE TABLE dbo.YourTable (YourColumn datetimeoffset(7));
INSERT INTO dbo.YourTable (YourColumn)
VALUES('2022-01-24T13:00:00+00:00'),
      ('2022-01-24T14:00:00+00:00'),
      ('2022-01-24T12:00:00+00:00');
GO

SELECT *
FROM dbo.YourTable
WHERE YourColumn = CONVERT(datetimeoffset(7),'2022-01-24T14:00:00+01:00');
GO
DROP TABLE dbo.YourTable;
GO

This returns the row with the value 2022-01-24T13:00:00.0000000 +00:00 because 2022-01-24T13:00:00.0000000 +00:00 and 2022-01-24T14:00:00+01:00 are the same time.