Convert datepart hour to local timezone

Question

Is there a way to combine these two T-SQL queries? I'm using SQL Server 2014.

The goal is to be able to have the output of the first query in the converted local timezone as opposed (or in addition) to UTC time. Basically looking to summarize records by hour in local timezone.

SELECT 
    DATEPART(HOUR, TimeUtc) AS UTCHour, Message, Application, 
    COUNT(Message) AS "Count"
FROM 
    [DB].[dbo].[TBL]
WHERE 
    timeutc BETWEEN '2022-10-21 00:00:00.000' AND '2022-10-21 23:59:59.000'
GROUP BY 
    DATEPART(HOUR, TimeUtc), Message, Application
HAVING 
    COUNT(Message) > 5
ORDER BY 
    UTCHour DESC, "Count" DESC

SELECT 
    CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, TimeUtc), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime 
FROM 
    [DB].[dbo].[TBL]
WHERE 
    timeutc BETWEEN '2022-10-21 00:00:00.000' AND '2022-10-21 23:59:59.000'

Example of current output: (UTC Hour represents HH of the TimeUTC field)

UTCHour	Message	Application	Count
23	Request failed.	WebAppv101	86756309
23	HTTP 500.	WebAppv101	8521
22	Layer 8 Error.	WebAppv100	21
22	ID10T Error.	WebAppv101	10

Example of desired output: (basically 23:00 UTC = 16:00 PDT, I am not concerned about the formatting, ie 16:00 vs 4PM, etc. as long as the output is in PDT).

PDTHour	Message	Application	Count
16	Request failed.	WebAppv101	86756309
16	HTTP 500.	WebAppv101	8521
15	Layer 8 Error.	WebAppv100	21
15	ID10T Error.	WebAppv101	10

Answer 1

select dateadd(MINUTE,DATEPART(TZ,SYSDATETIMEOFFSET()),sysutcdatetime());

This will return a datetime field in the current timezone, which is calculated from the sysutcdatetime, whith the addition of the number of minutes which is retruned from the sysdatetimeoffset.

NOTE: I do not know if the functions sysutcdatetime and sysdatetimeoffset are available in SQL Server 2014.

When you think this functionality is too long to type (or to remember), you can create a Function for this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UTCtoLocale] 
(
    @p1 datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @Result datetime

    SELECT @Result = dateadd(MINUTE,DATEPART(TZ,SYSDATETIMEOFFSET()),@p1);

    RETURN @Result

END
GO

After this, SELECT dbo.UTCtoLocale(sysutcdatetime()); should return the local time;

P.S. The name of this function is chosen, when you know a better name, then feel free to change it. 😉

Your SQL query using this function:

SELECT 
    DATEPART(HOUR, dbo.UTCtoLocale(TimeUtc)) AS LocalHour, Message, Application, 
    COUNT(Message) AS "Count"
FROM 
    [DB].[dbo].[TBL]
WHERE 
    dbo.UTCtoLocale(timeutc) BETWEEN '2022-10-21 00:00:00.000' AND '2022-10-21 23:59:59.000'
GROUP BY 
    DATEPART(HOUR, dbo.UTCtoLocale(TimeUtc)), Message, Application
HAVING 
    COUNT(Message) > 5
ORDER BY 
    LocalHour DESC, "Count" DESC

This function will get the results based on the local date '2022-10-21', your original was looking at the utc-date for this day.