SQL Server非标准日期格式筛选失效：日期转换与查询解决方案

问题核心表现 #

• 编写查询时无论调整何种日期筛选条件，返回结果集都不会随条件变化，无法按日期维度过滤数据
• 表内start_date字段存储的日期为日-月-年 小时:分钟格式，实际存储示例如下：

  29-12-2020 16:38
  31-12-2020 17:43
  

已尝试的无效操作 #

先后编写了多种日期区间筛选逻辑，均未达到预期过滤效果：

1. 带秒级时间的区间匹配

   select 
   start_date 
   from table_1
   where start_date between '01-01-2021 00:00:00' and '31-01-2021 00:00:00'
   

2. 匹配存储格式的分钟级时间区间

   select 
   start_date 
   from table_1
   where start_date between '01-01-2021 00:00' and '31-01-2021 00:00'
   

3. 仅按日期值做区间匹配

   select 
   start_date 
   from table_1
   where start_date between '01-01-2021' and '31-01-2021'
   

此外还尝试对start_date字段做CAST类型转换后追加筛选条件，依然没有实现有效过滤。

根本原因 #

• 多数场景下start_date为字符串类型存储，此时SQL Server按字典序对比字符串大小，而非按日期逻辑匹配区间，筛选自然完全失效
• 即便是手动做类型转换，未显式指定解析规则时，SQL Server会按默认的月-日-年顺序解析你存储的日-月-年格式字符串，得到的日期值完全错误，永远无法命中设定的区间

落地解决方法 #

• 长期优化方案：将start_date字段调整为原生日期类型（date/datetime/datetime2），从存储层面避免格式解析问题
• 临时查询可直接使用带格式指定的转换逻辑实现过滤，参考可运行写法：

  SELECT start_date 
  FROM table_1
  -- 按103样式（dd-mm-yyyy）强制解析字符串格式的日期值
  WHERE CONVERT(DATETIME, start_date, 103) 
    BETWEEN '2021-01-01' AND '2021-01-31 23:59:59'
  

  写法注意事项：
  • 筛选条件的固定值使用yyyy-mm-dd的ISO标准格式，该格式在SQL Server任意语言设置下都能被正确解析，不会出现歧义
  • 字段转换必须传入样式码：日-月-年格式对应样式码103，省略该参数会导致解析逻辑完全不符合存储格式
  • 区间结束时间要覆盖当日全时段，不要直接取当日零点，否则会漏掉筛选日期当天带具体时间的记录
• 若转换过程出现报错，可先用TRY_CONVERT函数筛查字段中的无效脏数据，清理后再执行筛选

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