Existe uma maneira mais concisa de converter uma data e hora UTC apenas em uma data local?

Esta resposta não leva em consideração as mudanças no horário de verão, a adição de segundos bissextos e é insensível a fusos horários que não são deslocamentos de hora inteira do UTC. Consulte minha segunda resposta a esta pergunta para obter uma maneira muito mais precisa de fazer isso para o SQL Server 2016 e superior.

Se você não é avesso a ter uma função fazendo o trabalho sujo, isso ajuda a tornar a instrução mais limpa:

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

Você poderia então fazer algo como:

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

O código acima truncará qualquer parte do tempo da entrada de data para a função (isso é por design). Portanto, conforme observado no início da minha resposta, todos os fusos horários que implementam minutos, como a Ilha do Príncipe Eduardo no Canadá (UTC -4:30), Índia (UTC -4:30) e Kathmandu (UTC +5:45 ), não verá resultados corretos.

Esteja ciente de que usar uma função como essa em um grande número de linhas será péssimo para o desempenho. A AT TIME ZONEconstrução mostrada abaixo não sofre tanto com esse problema, embora não seja de forma alguma uma bala de prata em termos de desempenho.

Normalmente, eu uso essa expressão

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

Mas no SQL Server 2008, isso simplifica para

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

Além de fornecer expressões alternativas aqui, se você é um purista T-SQL casado com funções DATE para manipular datas e horas, não acho que haja uma maneira mais concisa de obter a hora local além de usar uma função escalar.

No SQL Server 2016 e superior, use a AT TIME ZONE 'timezone'construção para converter valores de data e hora entre fusos horários. AT TIME ZONEleva em consideração as mudanças necessárias ao longo dos anos para estar em conformidade com coisas como horário de verão, etc.

Este primeiro bit configura alguns dados de amostra para usarmos:

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

Em seguida, pegamos os dados de amostra e os colocamos em uma tabela temporária intermediária, usando AT TIME ZONEpara criar uma cópia da coluna de data primeiro no fuso horário "Central Standard Time" e, em seguida, apenas a parte da data desse horário.

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

A consulta agrupada por data torna-se super fácil:

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;

A saída agrupada para uma amostra das linhas:

Os dados subjacentes para essas linhas:

Estes dados mostram a transição do horário padrão para o horário de verão em 2022: