Firebird 3中字符串转日期SQL查询的替代写法

SQL执行返回token unknown错误，是因为写法不符合Firebird 3的语法规则：

• Firebird 3不支持通用SQL里常见的substr函数，字符串截取需要使用substring(字段 from 起始位置 for 截取长度)的标准写法
• Firebird中日期字面量不需要通过DATE()函数转换，标准写法为DATE 'yyyy-MM-dd'
• 逐行对字段值做类型转换再筛选的写法，会导致字段上的索引失效，数据量大时查询性能较差

方案1：适配Firebird语法的日期转换写法（和原有逻辑完全对齐） #

仅替换不兼容的函数和日期写法，不需要调整原有筛选逻辑，适合需要严格做日期类型比较的场景：

SELECT SDC_TIME_SERVER_TIMEZONE 
FROM SDC_INVOICES 
WHERE CAST(
    SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 7 FOR 4) || '-' || 
    SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 4 FOR 2) || '-' || 
    SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 1 FOR 2) 
AS DATE) BETWEEN DATE '2022-01-01' AND DATE '2022-04-08'

方案2：字符串直接比较写法（性能更优） #

dd.MM.yyyy格式的日期字符串，截取年、月、日部分拼接为yyyyMMdd格式的定长字符串后，可以直接做字符串范围比较，不需要逐行做日期类型转换，能直接命中字段上的普通索引，数据量大时查询速度提升明显：

SELECT SDC_TIME_SERVER_TIMEZONE 
FROM SDC_INVOICES 
WHERE 
  SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 7 FOR 4) || 
  SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 4 FOR 2) || 
  SUBSTRING(SDC_TIME_SERVER_TIMEZONE FROM 1 FOR 2)
  BETWEEN '20220101' AND '20220408'

• 如果SDC_TIME_SERVER_TIMEZONE字段存在不符合dd.MM.yyyy格式的脏数据，比如长度不足10位、分隔符不是.、日期值非法，上述转换逻辑会触发类型转换错误，需要提前增加格式校验逻辑过滤脏数据。
• 在DBeaver中连接Firebird 3时，建议选择对应Firebird 3版本的JDBC驱动，低版本驱动也可能偶发语法兼容类报错。

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