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

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?

CodePudding user response：

With datetimeoffset columns you could use the AT TIME ZONE keywords when SELECT'ing the rows

[Edit]: Added DECLARE'ed variable to make TZ configurable

drop table if exists #YourTable;
go
CREATE TABLE #YourTable(
  YourColumn datetimeoffset(7));

insert #YourTable (YourColumn) VALUES
('2022-01-24T13:00:00 00:00'),
('2022-01-24T14:00:00 00:00'),
('2022-01-24T12:00:00 00:00');

declare @MyTZ       sysname=N'Central European Standard Time';

select *, YourColumn at time zone @MyTZ conv_tz
from #YourTable;

YourColumn                          conv_tz
2022-01-24 13:00:00.0000000  00:00  2022-01-24 14:00:00.0000000  01:00
2022-01-24 14:00:00.0000000  00:00  2022-01-24 15:00:00.0000000  01:00
2022-01-24 12:00:00.0000000  00:00  2022-01-24 13:00:00.0000000  01:00

To use in your formula would be like this:

DATEADD(MONTH, DATEDIFF(MONTH, 0, YourColumn at time zone @MyTZ), 0) YourColTZ

CodePudding user response：

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

CodePudding user response：

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.