SQL Server 2019 date类型比较异常问题求助

核心结论 #

SQL Server的date类型是以数值形式存储（3字节整数，代表从0001-01-01开始的天数），和显示格式完全无关，所以"12/1/2023"与"2023-12-01"这类格式差异不会影响比较结果。你遇到的问题属于存储或查询执行层面的异常，而非类型本身的格式问题。

可能的原因 #

1. 统计信息过时：SQL Server依赖统计信息生成查询计划，若统计信息过期，可能导致计划错误，返回不符合WHERE条件的行。
2. 数据页损坏：罕见但可能发生，表的数据页出现物理或逻辑损坏，导致字段存储值与显示值不一致。
3. 隐性类型转换异常：虽字段定义为date，但查询执行过程中可能因隐性转换（如关联其他类型、计算列影响等）导致比较逻辑出错。
4. 多源导入异常：数据从多源导入时，可能存在未被正确截断的非法数据（如带时间戳的字符串强制转date时，底层存储出现异常）。

分步解决方案 #

1. 验证底层存储值

先确认两列的实际存储是否一致，执行以下查询查看二进制存储内容：

SELECT 
    CAST(sIncEnd AS VARBINARY(3)) AS sIncEnd_bin,
    CAST(aIncEnd AS VARBINARY(3)) AS aIncEnd_bin,
    sIncEnd, 
    aIncEnd
FROM rates 
WHERE sIncEnd <> aIncEnd 
  AND DATEDIFF(day, sIncEnd, aIncEnd) = 0;

若二进制值相同，说明问题出在查询执行层面；若二进制值不同，说明存储的实际日期存在差异（仅显示时被格式化导致看起来相同）。

2. 更新统计信息

强制SQL Server重新生成正确的查询计划：

UPDATE STATISTICS dbo.rates;

更新后重新执行原查询，检查结果是否恢复正常。

3. 检查数据库完整性

排查是否存在数据页损坏：

DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;

若发现损坏，需根据DBCC提示修复（生产环境务必先备份）。

4. 修复异常数据

若确认存储值异常但显示相同，可通过转换后更新数据：

-- 操作前务必备份表！
UPDATE dbo.rates 
SET 
    sIncEnd = CONVERT(date, sIncEnd, 101),
    aIncEnd = CONVERT(date, aIncEnd, 101)
WHERE sIncEnd <> aIncEnd 
  AND DATEDIFF(day, sIncEnd, aIncEnd) = 0;

5. 迁移数据（终极方案）

若以上方法无效，可新建相同结构的表迁移数据后替换原表：

-- 1. 创建新表
CREATE TABLE [dbo].[rates_new](
    [sIncStart] [date] NULL,
    [sIncEnd] [date] NULL,
    [sPaidStart] [date] NULL,
    [sPaidEnd] [date] NULL,
    [aIncStart] [date] NULL,
    [aIncEnd] [date] NULL,
    [aPaidStart] [date] NULL,
    [aPaidEnd] [date] NULL
);

-- 2. 插入数据
INSERT INTO dbo.rates_new SELECT * FROM dbo.rates;

-- 3. 替换原表（需确保无业务写入）
BEGIN TRANSACTION;
DROP TABLE dbo.rates;
EXEC sp_rename 'dbo.rates_new', 'rates';
COMMIT TRANSACTION;

注意：生产环境执行此操作前必须做好全量备份，并在业务低峰期执行。

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