使用FORMAT生成的日期列在WHERE子句中筛选失效的问题求助

问题根源 #

你用FORMAT函数把datetime类型的DateStart转成了nvarchar字符串类型，而字符串的比较是按字符顺序逐位对比，完全不遵循日期的逻辑规则。比如'31/08/2023'作为字符串，第一位字符3比'01/09/2023'的第一位0大，会被误判为满足>= '01/09/2023'，但实际日期是8月31日，早于9月1日——这就是结果出错的核心原因。

错误写法分析 #

你尝试的几种写法都存在问题：

• WHERE DateStart >= '01/09/2023'：字符串对比逻辑错误，会误判跨月的日期
• WHERE DateStart >= 2023-09-01：数据库会把这个表达式计算为2023-9-1=2013，相当于判断字符串是否大于等于'2013'，完全偏离需求
• WHERE DateStart >= 01-09-2023：同理，计算结果为1-9-2023=-2031，判断逻辑完全错误
• WHERE DateStart > FORMAT(01/09/2023, 'd', 'en-gb')：01/09/2023在这里是除法运算，结果约为0.000054，FORMAT后会转成'01/01/1900'，相当于筛选所有晚于1900年1月1日的数据，自然返回几乎全部记录

解决方案 #

方案一：直接基于原datetime列筛选（推荐）

不要用格式化后的字符串列做筛选，直接对原datetime类型的mxspr.DateStart处理，数据库会按日期逻辑对比，效率更高且不会出错。

正确写法示例：

-- 直接用日期字面量（推荐用yyyy-MM-dd格式，不受语言设置影响）
WHERE mxspr.DateStart >= '2023-09-01'

-- 如果需要严格排除时间部分（只对比日期），可以转成DATE类型
WHERE CAST(mxspr.DateStart AS DATE) >= '2023-09-01'

方案二：必须用格式化字符串筛选时（不推荐）

如果因业务需求必须用格式化后的字符串，要使用可按字符顺序正确排序的日期格式，比如yyyy-MM-dd（年-月-日），这种格式的字符串对比逻辑和日期逻辑一致：

SELECT 
    -- 其他列...
    FORMAT(mxspr.DateStart, 'yyyy-MM-dd') AS DateStart
FROM ...
WHERE FORMAT(mxspr.DateStart, 'yyyy-MM-dd') >= '2023-09-01'

⚠️ 注意：这种写法会导致原datetime列的索引失效，查询效率大幅降低，仅在特殊场景下使用。

修正后的完整SQL代码 #

SELECT r.Reference, r.flag_batch_ready,
    CASE 
        WHEN p.Tenure IS NULL THEN r.Tenure
        ELSE p.Tenure
    END AS Property_Tenure,
    mxspr.Status AS Max_Survey_Status,
    CASE
        WHEN r.GSEHousePlanUploaded IS NULL THEN 'No'
        WHEN r.GSEHousePlanUploaded = 0  THEN 'No'
        WHEN r.GSEHousePlanUploaded = 1 THEN 'Yes'
        ELSE 'to check'
    END AS HP_uploaded,
    -- 展示层可以用FORMAT转成想要的日期格式
    FORMAT(mxspr.DateStart, 'd', 'en-gb') AS DateStart

FROM hug2.ReferralUpdates r
    LEFT JOIN hug2.max_survey_status_per_ref mxspr
        ON r.Reference = mxspr.Reference
    LEFT JOIN hug2.max_propertyId_per_ref p
        ON r.Reference = p.Reference

-- 用原datetime列做筛选，确保逻辑正确
WHERE CAST(mxspr.DateStart AS DATE) >= '2023-09-01'

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