Snowflake存储过程用RLIKE转换VARCHAR为DATE时执行不一致

1. Snowflake查询优化器不会严格遵循CASE语句的分支短路逻辑，会提前对所有分支的表达式做预校验，哪怕某行数据已经匹配上WHEN条件，ELSE分支的TO_DATE(RECORDED_DATE)也可能被提前执行，遇到2018/03/29这类不符合默认日期格式的字符串就会直接抛出报错。
2. 现有正则规则覆盖不全，仅匹配了YYYY/MM/DD格式，漏掉了M/D/YYYY这类月/日/年的短格式，就算CASE顺序正常也会有部分数据转换失败。
3. 存储过程中JavaScript模板字符串的反斜杠转义需要注意层级，避免正则规则传到SQL层后失效。

直接使用Snowflake原生支持的多格式参数TRY_TO_DATE函数，无需手动写正则和CASE判断，函数会按传入的格式顺序自动匹配转换，转换失败会返回NULL而非抛出报错：

CREATE OR REPLACE PROCEDURE LOAD_TABLE_B(LD VARCHAR)
RETURNS STRING 
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var insert_command =`INSERT INTO TABLE_B
SELECT 
  START_DATE,
  END_DATE,
  -- 按优先级传入所有可能的日期格式，自动匹配
  TRY_TO_DATE(RECORDED_DATE, 'YYYY-MM-DD', 'YYYY/MM/DD', 'MM/DD/YYYY', 'M/D/YYYY') AS RECORDED_DATE,
  HASH(S.$1,S.$2,S.$3) AS CHECKSUM_HASH    
FROM TABLE_A  S;
`;
try {
    snowflake.execute({sqlText:insert_command});
    return "Success";   
} 
catch (err)  {
    throw err;    
}
$$ ;

如果需要过滤转换失败的行，可以在SELECT语句后添加WHERE条件：WHERE TRY_TO_DATE(RECORDED_DATE, 'YYYY-MM-DD', 'YYYY/MM/DD', 'MM/DD/YYYY', 'M/D/YYYY') IS NOT NULL。

补全所有格式的正则规则，同时在ELSE分支套TRY_TO_DATE避免预校验报错：

CASE
  WHEN RECORDED_DATE RLIKE '^\\d{4}-\\d{2}-\\d{2}$' THEN TO_DATE(RECORDED_DATE, 'YYYY-MM-DD')
  WHEN RECORDED_DATE RLIKE '^\\d{4}/\\d{2}/\\d{2}$' THEN TO_DATE(RECORDED_DATE, 'YYYY/MM/DD')
  WHEN RECORDED_DATE RLIKE '^\\d{1,2}/\\d{1,2}/\\d{4}$' THEN TO_DATE(RECORDED_DATE, 'MM/DD/YYYY')
  ELSE TRY_TO_DATE(RECORDED_DATE)
END AS RECORDED_DATE

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