7

I am trying to write a query that groups records based on the local date part only of a UTC datetime field.

For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as 10/18/2012, since my local time is EST (-5h) and I'm only interested in the date portion of the field.

I know I can use DateAdd(day, DateDiff(day, 0, MyDate), 0) to get the date part only from the datetime field, and I can use DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate) to convert a UTC datetime to a local date time.

But combining the two is seriously offending me.

Is there a better way than this to get just the Date part of a UTC DateTime field, converted to local time, in SQL Server 2005?

  SELECT DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
       , Count(*)
    FROM MyTable
GROUP BY DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
0

3 Answers 3

6

This answer does not take into account Daylight Savings Time changes, the addition of leap seconds, and is insensitive to time zones that are not whole-hour offsets from UTC. See my 2nd answer on this question for a far more accurate way of doing this for SQL Server 2016 and above.

If you're not averse to having a function do the dirty work, this helps make the statement cleaner:

CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

You could then do something like:

SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);

The above code will truncate any time portion of the date input to the function (this is by design). Therefore, as noted at the start of my answer, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45), will not see correct results.

Be aware using a function like this on a large number of rows will be terrible for performance. The AT TIME ZONE construct as shown below does not suffer as much from that problem, although it is by no means a silver bullet, performance-wise.

0
3

Normally, I use this expression

CAST(LEFT(GetDate() - GetUtcDate() + MyUtcDate, 11) AS DATETIME)

But on SQL Server 2008, that simplifies to

CAST(GetDate() - GetUtcDate() + MyUtcDate AS DATE)

Other than giving alternative expressions here, if you're a T-SQL purist married to DATE functions to manipulate datetimes, I don't think there's a more concise way to get the local time beyond using a scalar function.

0

In SQL Server 2016 and above, use the AT TIME ZONE 'timezone' construct to convert datetime values between time zones. AT TIME ZONE takes into account the changes necessary over the years to be compliant with things like Daylight Savings time, etc.

This first bit sets up some sample data for us to use:

USE [tempdb];
GO

DROP TABLE IF EXISTS [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [my_table_id]           int         NOT NULL
        PRIMARY KEY
        CLUSTERED
        IDENTITY(-2147483648, 1)
    , [utc_date_column]     datetime    NOT NULL
);

INSERT INTO [dbo].[my_table] ([utc_date_column])
SELECT TOP(50039) DATEADD(SECOND, CRYPT_GEN_RANDOM(4) % 86400, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT (NULL))) - 1, '1900-01-01T00:00:00.000'))
FROM [sys].[syscolumns] sc1
    CROSS JOIN [sys].[syscolumns] sc2;
GO

Next, we take the sample data and stuff it into an intermediate temporary table, using AT TIME ZONE to create a copy of the date column first in the "Central Standard Time" time zone, and then just the date portion of that time.

DROP TABLE IF EXISTS #intermediate_results;
CREATE TABLE #intermediate_results
(
      [original_column]             datetimeoffset(7)   NOT NULL
    , [original_column_local_time]  datetimeoffset(7)   NOT NULL
    , [local_time_date_only]        date                NOT NULL
        INDEX irc CLUSTERED
);

INSERT INTO #intermediate_results WITH (TABLOCKX)
(
      [original_column]
    , [original_column_local_time]
    , [local_time_date_only]
)
SELECT 
      [original_column]             = mt.[utc_date_column]
    , [original_column_local_time]  = mt.[utc_date_column] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'
    , [local_time_date_only]        = CONVERT(date, mt.[utc_date_column] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')
FROM dbo.[my_table] mt
ORDER BY mt.[utc_date_column] ASC;
GO

Querying grouped by the date then becomes super easy:

SELECT 
      ir.local_time_date_only
    , [rows_on_this_local_day] = COUNT_BIG(1)
FROM #intermediate_results ir
GROUP BY ir.local_time_date_only;

The grouped output for a sample of the rows:

local_time_date_only rows_on_this_local_day
1899-12-30 1
1899-12-31 1
1900-01-02 1
1900-01-04 2
1900-01-05 1
1900-01-06 1
1900-01-07 1
1900-01-09 2

The underlying data for those rows:

original_column original_column_local_time local_time_date_only
1899-12-31 00:27:57.0000000 +00:00 1899-12-30 18:27:57.0000000 -06:00 1899-12-30
1900-01-01 03:58:45.0000000 +00:00 1899-12-31 21:58:45.0000000 -06:00 1899-12-31
1900-01-02 07:33:52.0000000 +00:00 1900-01-02 01:33:52.0000000 -06:00 1900-01-02
1900-01-04 21:49:23.0000000 +00:00 1900-01-04 15:49:23.0000000 -06:00 1900-01-04
1900-01-05 03:05:18.0000000 +00:00 1900-01-04 21:05:18.0000000 -06:00 1900-01-04
1900-01-06 04:13:23.0000000 +00:00 1900-01-05 22:13:23.0000000 -06:00 1900-01-05
1900-01-06 19:21:57.0000000 +00:00 1900-01-06 13:21:57.0000000 -06:00 1900-01-06
1900-01-07 14:35:54.0000000 +00:00 1900-01-07 08:35:54.0000000 -06:00 1900-01-07
1900-01-09 21:02:35.0000000 +00:00 1900-01-09 15:02:35.0000000 -06:00 1900-01-09
1900-01-09 17:17:39.0000000 +00:00 1900-01-09 11:17:39.0000000 -06:00 1900-01-09

This data shows the transition from Standard Time to Daylight Savings Time for 2022:

original_column original_column_local_time local_time_date_only
2022-03-12 17:22:25.0000000 +00:00 2022-03-12 11:22:25.0000000 -06:00 2022-03-12
2022-03-13 07:49:40.0000000 +00:00 2022-03-13 01:49:40.0000000 -06:00 2022-03-13
2022-03-13 22:43:44.0000000 +00:00 2022-03-13 17:43:44.0000000 -05:00 2022-03-13
2022-03-15 03:20:19.0000000 +00:00 2022-03-14 22:20:19.0000000 -05:00 2022-03-14
2022-03-16 23:23:51.0000000 +00:00 2022-03-16 18:23:51.0000000 -05:00 2022-03-16
2022-03-16 08:00:17.0000000 +00:00 2022-03-16 03:00:17.0000000 -05:00 2022-03-16
2022-03-17 10:48:36.0000000 +00:00 2022-03-17 05:48:36.0000000 -05:00 2022-03-17
2022-03-18 15:35:20.0000000 +00:00 2022-03-18 10:35:20.0000000 -05:00 2022-03-18
2022-03-20 11:04:19.0000000 +00:00 2022-03-20 06:04:19.0000000 -05:00 2022-03-20
2022-03-21 01:39:34.0000000 +00:00 2022-03-20 20:39:34.0000000 -05:00 2022-03-20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.