SQL技术问询：获取指定日期范围及两年前同月同期数据并修正日期筛选偏差

嘿，我看你在筛选用于对比分析的历史数据时遇到了范围不符的问题，同时还想优化日期转换的写法，这就帮你搞定：

一、修正查询条件，拿到你要的截止到2019年10月的数据 #

你现在的WHERE子句里，用年份小于当前减2的判断，会把2019年全年的数据都捞出来，这就是为什么返回了12月的记录。要精准拿到对应两年前当月同周的截止日期（比如你说的2019-10-23），得直接对date_key做日期范围的限制，而不是只卡年份。

首先，你的date_key是YYYYMMDD格式的数字，我们可以高效地把它转成日期类型来做比较。修正后的SQL如下：

SELECT 
    tilr.BusinessUnitID,
    emph.employeeID,
    CONVERT(varchar(10), CAST(tilr.date_key AS char(8)), 101) AS ConvertDate,
    tilr.paidhr AS 'Paid hr'
FROM [dbo].[location] tilr
INNER JOIN [dbo].[Employee] emph 
    ON emph.employeeID = tilr.employeeID 
    AND emph.businessunitid = tilr.BusinessUnitID 
    AND emph.date_key = tilr.date_key
WHERE 
    tilr.date_key >= 20190701
    -- 把date_key转成日期后，小于等于两年前的当前日期
    AND CAST(tilr.date_key AS char(8)) <= CONVERT(char(8), DATEADD(YEAR, -2, GETDATE()), 112)

如果你的截止日期是固定的2019-10-23，那直接写死更简单：

AND tilr.date_key <= 20191023

要是你需要严格匹配同月同周（比如当前是2021年第43周，就取2019年第43周的数据），可以用DATEPART(ISO_WEEK)来精准匹配周数，同时年份减2：

WHERE 
    tilr.date_key >= 20190701
    -- 匹配两年前的同周范围
    AND DATEPART(ISO_WEEK, CAST(tilr.date_key AS char(8))) = DATEPART(ISO_WEEK, GETDATE())
    AND DATEPART(YEAR, CAST(tilr.date_key AS char(8))) = DATEPART(YEAR, DATEADD(YEAR, -2, GETDATE()))

二、日期转换的优化建议 #

你现在写的日期转换convert(varchar(10), cast(cast(tilr.date_key as varchar(10)) as date), 101)有点绕，其实可以简化很多：

• 因为date_key是YYYYMMDD格式的数字，直接转成char(8)（固定长度比varchar更高效），再转成日期格式化：CONVERT(varchar(10), CAST(tilr.date_key AS char(8)), 101)
• 如果只是用于日期运算（比如比较大小），甚至不需要格式化，直接CAST(tilr.date_key AS date)就行，SQL Server能直接识别这种数字格式的日期
• 另外，如果表数据量比较大，建议给date_key字段加个索引，这样日期范围查询的速度会快很多

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