SQL Server：先转换时区再按月份聚合数据的实现问题

方法1：用CTE提前转换时间（推荐） #

先通过公共表表达式把UTC时间转成中欧标准时间，并生成月份起始日期，再在外层做聚合，这样就能直接用转换后的字段分组：

WITH ConvertedData AS (
    SELECT
        [VALUE],
        DATEFROMPARTS(
            YEAR(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
            MONTH(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
            1
        ) AS TIMESTAMP_LOCAL
    FROM [DB].[TABLE]
)
SELECT
    SUM([VALUE]) AS Required_Value,
    TIMESTAMP_LOCAL
FROM ConvertedData
GROUP BY TIMESTAMP_LOCAL

方法2：在GROUP BY中重复完整表达式 #

如果不想用CTE，也可以直接把SELECT里的日期构造表达式完整复制到GROUP BY中：

SELECT
    SUM([VALUE]) AS Required_Value,
    DATEFROMPARTS(
        YEAR(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
        MONTH(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
        1
    ) AS TIMESTAMP_LOCAL
FROM [DB].[TABLE]
GROUP BY
    DATEFROMPARTS(
        YEAR(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
        MONTH(CONVERT(DATETIME2(3), TIMESTAMP_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')),
        1
    )

关键说明 #

• SQL Server中GROUP BY的执行顺序早于SELECT，因此SELECT里的别名无法直接用于GROUP BY。
• AT TIME ZONE语法会自动处理中欧时间的夏令时切换（CET/CEST），确保转换结果准确。

内容的提问来源于stack exchange，提问作者Sher