Snowflake中删除无效日期触发数值格式不识别报错求助

问题根源 #

你执行的delete from reconn_output_1 where reconn_date = 14/12/2023会被数据库解析为数值除法运算（14÷12÷2023），而非日期字符串匹配；同时RECONN_DATE列大概率是字符串类型，存储了不符合日期规范的内容，导致数据库无法识别为有效日期触发报错。

解决方案 #

1. 修正单条删除的语法错误 #

如果要删除指定的无效日期字符串，需给日期值加单引号，让数据库识别为字符串匹配：

DELETE FROM reconn_output_1 WHERE reconn_date = '14/12/2023';

2. 批量删除所有无效日期 #

如果要一次性删除所有月份超过12的无效数据，根据不同数据库使用对应的字符串拆分函数：

• MySQL/MariaDB：

  DELETE FROM reconn_output_1 
  -- 假设日期格式为 DD/MM/YYYY，取第二个部分为月份
  WHERE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(reconn_date, '/', 2), '/', -1) AS UNSIGNED) > 12
  -- 可额外判断日期部分是否合法（比如日>31）
     OR CAST(SUBSTRING_INDEX(reconn_date, '/', 1) AS UNSIGNED) > 31;
  

• PostgreSQL：

  DELETE FROM reconn_output_1 
  WHERE CAST(SPLIT_PART(reconn_date, '/', 2) AS INTEGER) > 12
     OR CAST(SPLIT_PART(reconn_date, '/', 1) AS INTEGER) > 31;
  

• SQL Server：

  DELETE FROM reconn_output_1 
  WHERE CAST(STRING_SPLIT(reconn_date, '/')[2] AS INT) > 12
     OR CAST(STRING_SPLIT(reconn_date, '/')[1] AS INT) > 31;
  

3. 预防建议 #

后续建议将RECONN_DATE列改为日期类型（如DATE），并在插入/更新时做日期合法性校验，避免无效数据存入。

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