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?

*" all values are UTC (+00:00) - but contain data from multiple timezones."* That statement conflicts with itself. If all the values are UTC+0 then they aren't from different timezones... — Thom A, Jan 24 '22 at 12:52
@Larnu I meant that data is inserted from different timezones, then converted to UTC and saved to the database - i've removed that part i don't think its relevant ayways? — sommmen, Jan 24 '22 at 13:00

SteveC · Accepted Answer · 2022-01-24T13:54:53.653

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

Yes this seems to be what i'm looking for - thank you! – sommmen Jan 24 '22 at 14:00 — sommmen, Jan 24 '22 at 14:00

score 1 · Answer 2 · answered Jan 24 '22 at 13:18

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

answered Jan 24 '22 at 13:18

Serg

22,285
5
21
48

Thanks - this seems to work, but does not seem to take DST into account. quite useful however if i want to ignore that. – sommmen Jan 24 '22 at 14:00

score 0 · Answer 3 · answered Jan 24 '22 at 13:04

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.

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

3 Answers3