The query in snowflake,
select date_column, try_to_date(date_column) from tablename;
tends to mess up the intended dates as shown below:
01-NOV-18 ____________ 0018-11-01 (desired output 2018-11-01) 09-JAN-19 ____________ 0019-01-09 2018-11-03 20:44:54 __ 2018-11-03 2018-09-03 00:00:00 __ 2018-09-03 2018-08-22 19:38:41 __ 2018-08-22
This is similar to Snowsql two digit century start date cast issue, but with multiple date formats in the input column.
Advertisement
Answer
You could use a coalesce with the try_to_date. The key is to specifically put a date format in the try_to_date function so that it returns null if it can’t convert a date that doesn’t match the format. When the date doesn’t match the format it’ll fall back to the next method you specify and you can continue until you covered all your different date formats. Try something like:
select date_column, coalesce(try_to_date(date_column, 'YYYY-MM-DD HH:MI:SS'), try_to_date(date_column, 'DD-MON-YY')) from tablename;
This returns:
+-------------------+----------------------------------------------------------------------------------------------+ |INPUT_DATE |COALESCE(TRY_TO_DATE(INPUT_DATE, 'YYYY-MM-DD HH:MI:SS'), TRY_TO_DATE(INPUT_DATE, 'DD-MON-YY'))| +-------------------+----------------------------------------------------------------------------------------------+ |01-NOV-18 |2018-11-01 | |09-JAN-19 |2019-01-09 | |2018-11-03 20:44:54|2018-11-03 | |2018-09-03 00:00:00|2018-09-03 | |2018-08-22 19:38:41|2018-08-22 | +-------------------+----------------------------------------------------------------------------------------------+