使用RLIKE的Snowflake存储过程中VARCHAR值到DATE的REGEX转换不一致

Question

我正在尝试通过Snowflake存储过程将具有混合日期格式的列(2017/12/10、2018-02-27、8/18/2017(转换为YYYY-MM-DD格式。当通过CALL语句执行时，它执行case语句的顺序似乎不一致。

表A:

CREATE TABLE TABLE_A
(
START_DATE VARCHAR,
END_DATE VARCHAR,
RECORDED_DATE VARCHAR);
INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','2018/03/29');
INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','2018-02-27');
INSERT INTO TABLE_A VALUES ('2021-11-09', '2021-11-09','8/18/2017');

存储过程：

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
,CASE WHEN RECORDED_DATE RLIKE '\d{4}/\d{2}/\d{2}' THEN TO_DATE(RECORDED_DATE, 'YYYY/MM/DD')
ELSE TO_DATE(RECORDED_DATE)
END  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;    
}
$$ ;
CALL LOAD_TABLE_B(1);

错误消息：

Execution error in store procedure LOAD_TABLE_B: Date '2018/03/29' is not recognized At Snowflake.execute, line 18 position 11

Answer 1

因为您在存储过程中运行这个。查询本身在执行之前有一轮额外的解析和字符转义。这意味着您需要额外的反斜杠。语法变得近乎愚蠢，但这正是您所需要的。

var insert_command =`CREATE OR REPLACE TABLE TABLE_B AS
SELECT START_DATE
,END_DATE
,CASE WHEN RECORDED_DATE RLIKE '\\d{4}/\\d{2}/\\d{2}' THEN TO_DATE(RECORDED_DATE, 'YYYY/MM/DD')
ELSE TO_DATE(RECORDED_DATE)
END  AS RECORDED_DATE
,HASH(S.$1,S.$2,S.$3) AS CHECKSUM_HASH    
FROM TABLE_A  S;
`;

Answer 2

在CASE中不使用RLIKE的另一个解决方案是在COALESCE 中嵌套TRY_to_DATE格式

COALESCE(TRY_TO_DATE(recorded_date), TRY_TO_DATE(recorded_date, 'YYYY/MM/DD')) AS RECORDED_DATEAS recorded_date