REGEX conversion of VARCHAR value to DATE in Snowflake stored procedure using RLIKE not consistent-CodePudding

I am trying to convert a column that has mixed date formats - 2017/12/10, 2018-02-27, 8/18/2017 to YYYY-MM-DD format through a Snowflake stored procedure. When executing through CALL statement, the order in which it executes the case statement doesn't seem to be consistent.

TableA:

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');

Stored procedure:

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);

Error message:

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

CodePudding user response：

Because you're running this in a stored procedure. The query itself has an extra round of parsing and character escaping before it is executed. meaning you need extra backslashes. The syntax gets borderline silly, but this is what you need.

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;
`;

CodePudding user response：

another solution instead of using RLIKE in a CASE is to just nest the TRY_TO_DATE formats in a COALESCE

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