SQL使用BETWEEN筛选日期区间报字符串转数值错误如何解决

问题根因 #

你当前使用的筛选语句如下：

where DATE_CUSTOMER_ACCOUNT_SETUP between '01/03/2021' and '28/02/2022'

触发A character string failed conversion to a numeric value报错的核心原因是日期字面量隐式转换失败：

• 你传入的日期字符串是DD/MM/YYYY格式，但当前数据库/会话默认的日期解析规则不匹配该格式，数据库尝试将斜杠分隔的字符串转为数值类型做运算时直接触发错误，和DATE_CUSTOMER_ACCOUNT_SETUP字段本身的类型定义无关。
• 额外注意：就算格式匹配，直接用between筛选带时分秒的日期时间类型字段，大概率会漏算结束日期当天0点之后的数据。

可直接落地的解决方案 #

方案1：显式指定日期格式做转换（最稳妥，不受会话默认配置影响） #

不要依赖数据库的隐式转换规则，用对应数据库的日期转换函数显式声明输入字符串的格式，不同数据库的适配写法如下：

• Teradata（该报错最常见于Teradata引擎）：

where DATE_CUSTOMER_ACCOUNT_SETUP 
  between TO_DATE('01/03/2021','DD/MM/YYYY') 
      and TO_DATE('28/02/2022','DD/MM/YYYY')

• MySQL：

where DATE_CUSTOMER_ACCOUNT_SETUP 
  between STR_TO_DATE('01/03/2021','%d/%m/%Y') 
      and STR_TO_DATE('28/02/2022','%d/%m/%Y')

• SQL Server：

where DATE_CUSTOMER_ACCOUNT_SETUP 
  between CONVERT(DATE,'01/03/2021',103) 
      and CONVERT(DATE,'28/02/2022',103)

方案2：用ANSI标准无歧义日期格式 #

所有兼容ANSI SQL标准的数据库都默认支持YYYY-MM-DD格式的日期字面量，不需要额外写转换函数，也不会因为会话的日期格式配置变化触发转换错误，改写后语句如下：

where DATE_CUSTOMER_ACCOUNT_SETUP between '2021-03-01' and '2022-02-28'

更优替代写法（避免漏数问题） #

如果DATE_CUSTOMER_ACCOUNT_SETUP是DATETIME/TIMESTAMP类型（即存储值带时分秒部分），不要用between做范围筛选，改成半开区间判断，既能避免隐式转换问题，也不会漏算结束日期当天的非0点数据：

-- 以Teradata写法为例，其他数据库替换为对应转换函数即可
where DATE_CUSTOMER_ACCOUNT_SETUP >= TO_DATE('01/03/2021','DD/MM/YYYY')
  and DATE_CUSTOMER_ACCOUNT_SETUP < TO_DATE('01/03/2022','DD/MM/YYYY')

注意：结束条件写筛选周期结束日的下一天（即2022年3月1日），用小于号判断，会自动覆盖2022年2月28日全天所有时间点的数据，不需要额外判断时分秒。

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