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)

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).

CodePudding user response：

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.