SQL用to_char+like筛选6/27/2022日期无结果问题求助

问题原因 #

• 匹配规则和实际值不符：从你第一条分组查询的返回结果可以看到，6月27日的日期字符串表现为6/27/2022，月份是不带前导零的6，你写的like匹配条件是%06/27/2022%，多写了一个不存在的前导0，完全匹配不到对应值。
• 日期转换逻辑不可靠：你调用to_char(DATE)时没有指定第二个格式参数，数据库会默认使用当前会话的日期格式配置做转换，不同会话、不同环境的默认格式可能不一致，转换出来的字符串不一定是M/DD/YYYY hh:mi:ss PM的形式，用字符串模糊匹配日期的逻辑本身稳定性极差。
• 表名存在笔误风险：你第一条分组查询用的表是TABLE1，第二条筛选查询写的表是TABLE，如果不是输入时的笔误，查错表也会导致返回结果为空。

解决方案 #

优先推荐用日期范围做筛选，完全避开字符串格式的坑，查询效率也更高：

-- 筛选2022年6月27日全天的所有记录，兼容DATE字段带/不带时分秒的场景
SELECT * FROM TABLE1
WHERE DATE >= TO_DATE('2022-06-27', 'YYYY-MM-DD')
  AND DATE < TO_DATE('2022-06-28', 'YYYY-MM-DD')

这个写法不会受数据库日期格式配置影响，还能命中DATE字段上建的索引，是日期筛选的标准写法。

如果确实需要用字符串匹配的方式实现，要明确指定to_char的转换格式，同时修正匹配值的前导零问题：

-- 指定转换为月不带前导零的格式，匹配对应日期
SELECT * FROM TABLE1
WHERE TO_CHAR(DATE, 'M/DD/YYYY') LIKE '6/27/2022%'

注意：不要用无格式参数的to_char做日期字符串转换，只要会话的默认日期格式被修改，你的查询逻辑就会直接失效。

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