Snowflake场景下检索含错误日期格式记录的问题求助

问题原因 #

第二种查询的LIKE模式'2019-07 %'要求匹配的字符串必须以2019-07 （末尾带空格）开头，但你存储的两种日期格式都不符合这个规则：

• 错误格式2019-07末尾没有空格
• 规范ISO格式2019-07-xxTxx:xx:xx.xxx中，2019-07后紧跟的是短横线-而非空格
  因此没有匹配结果。

解决方案 #

根据不同使用场景选择对应写法：

• 场景1：仅查询包含特定年月的所有记录
  你第一种写法SELECT * FROM data where data.date LIKE '%2019-07%';已经可以满足需求，前后的%通配符可以匹配任意位置的目标字符串。
• 场景2：需要前缀匹配提升查询精准度
  删掉模式里多余的空格即可，匹配所有以2019-07开头的字符串：

  SELECT * FROM data where data.date LIKE '2019-07%';
  

• 场景3：批量检索所有不符合ISO完整格式的错误日期
  用Snowflake内置校验函数比LIKE准确性更高，自动识别所有格式错误的日期值：

  -- 匹配所有无法转换为指定ISO格式的日期记录
  SELECT * FROM data 
  WHERE TRY_TO_TIMESTAMP(date, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') IS NULL;
  
  -- 仅匹配长度为7位的YYYY-MM格式错误日期，可缩小范围
  SELECT * FROM data 
  WHERE LENGTH(date) = 7
  AND TRY_TO_TIMESTAMP(date, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') IS NULL;
  

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