datetimeoffset转DateTime：近两周23:00至次日07:00查询报错求助

我需要查询包含datetimeoffset类型字段CreatedOn的表，获取近两周内每天23:00（前一天）到次日07:00的所有记录。尝试了以下SQL（暂未添加两周过滤条件）却报错「Operand type clash: datetimeoffset is incompatible with int」，但单独执行SWITCHOFFSET语句正常：

原错误SQL：

SELECT * 
FROM Occurrences 
 WHERE SWITCHOFFSET(CreatedOn,'+01:00') >= DATEADD(hh,23,DATEADD(day, DATEDIFF(day, 0, 
  SWITCHOFFSET(CreatedOn,'+01:00') - 1), 0))
 AND SWITCHOFFSET(CreatedOn,'+01:00') <    DATEADD(hh,7,DATEADD(day, DATEDIFF(day, 0, 
  SWITCHOFFSET(CreatedOn,'+01:00')), 0))
 ORDER BY CreatedOn DESC 

CreatedOn字段类型为datetimeoffset(7)，示例值：2022-11-15 09:24:13.6096718 +01:00，以下语句可正常执行：

SELECT TOP 1 SWITCHOFFSET(CreatedOn,'+01:00') FROM Occurrences ORDER BY CreatedOn DESC

错误根源 #

报错核心原因是SWITCHOFFSET(CreatedOn,'+01:00') - 1这部分：datetimeoffset类型无法直接与整数做减法运算，你想表达的是“减1天”，但SQL Server无法识别该操作，必须用DATEADD函数来处理日期偏移。

修正后的查询语句 #

先将CreatedOn转换为目标时区，再计算时间范围，同时加入近两周的过滤条件，这里用CTE简化重复计算：

SELECT *
FROM Occurrences
-- CTE统一转换时区，避免重复计算
WITH CTE_Converted AS (
    SELECT 
        *,
        SWITCHOFFSET(CreatedOn, '+01:00') AS CreatedOn_UTC1
    FROM Occurrences
    -- 近两周过滤：只保留UTC+1时区下最近14天的记录
    WHERE SWITCHOFFSET(CreatedOn, '+01:00') >= DATEADD(day, -14, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')
)
SELECT *
FROM CTE_Converted
WHERE 
    -- 判断记录是否落在「前一天23:00 到 当天07:00」区间
    CreatedOn_UTC1 >= DATEADD(hour, 23, DATEADD(day, DATEDIFF(day, 0, CreatedOn_UTC1) - 1, 0))
    AND CreatedOn_UTC1 < DATEADD(hour, 7, DATEADD(day, DATEDIFF(day, 0, CreatedOn_UTC1), 0))
ORDER BY CreatedOn DESC;

如果不想用CTE，也可以直接写为：

SELECT *
FROM Occurrences
WHERE
    -- 近两周过滤
    SWITCHOFFSET(CreatedOn, '+01:00') >= DATEADD(day, -14, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')
    -- 时间区间判断
    AND SWITCHOFFSET(CreatedOn, '+01:00') >= DATEADD(hour, 23, DATEADD(day, DATEDIFF(day, 0, SWITCHOFFSET(CreatedOn, '+01:00')) - 1, 0))
    AND SWITCHOFFSET(CreatedOn, '+01:00') < DATEADD(hour, 7, DATEADD(day, DATEDIFF(day, 0, SWITCHOFFSET(CreatedOn, '+01:00')), 0))
ORDER BY CreatedOn DESC;

补充说明 #

1. 用CTE的好处是避免重复调用SWITCHOFFSET，既提升查询效率，也让代码更易读。
2. 近两周的过滤用GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'是为了确保时区一致，避免跨时区计算误差。
3. DATEDIFF(day, 0, CreatedOn_UTC1)的作用是将日期截断到当天零点，再通过DATEADD计算出目标时间点（前一天23点、当天7点）。

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