2

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
vvazza
  • 421
  • 7
  • 21
  • A) You've definitely found something perplexing. your query as a select works fine, but as an insert, it's throwing that error. I'm working at it. B) Your first question didn't get deleted. It's still there :P the one answer got deleted is all. – David Garrison Nov 10 '21 at 03:01
  • Am not sure why it works in standalone SQL but not executed through Stored procedure.Oh sorry ! I got a pop up saying something was deleted. This question has more details. I will remove the other question. – vvazza Nov 10 '21 at 03:03
  • 1
    I managed to get myself confused because I was running the script as is (and it was working) then I was running the version that the procedure was actually generating (which wasn't working). But it was due to an encoding issue rather than an insert/select thing. – David Garrison Nov 10 '21 at 03:08
  • Well done :) Much appreciated. – vvazza Nov 10 '21 at 03:09

2 Answers2

2

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;
`;
David Garrison
  • 2,546
  • 15
  • 25
  • happy to help. For a little bit of context on these sorts of issues, there's a note on escaped backslashes here: https://docs.snowflake.com/en/sql-reference/functions-regexp.html#escape-characters-and-caveats – David Garrison Nov 10 '21 at 03:11
2

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
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45