How to deal with Snowflake to_date cast issue with multiple date formats in same column?

user_1745201 0
Viewed 356

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.

snowflake-cloud-data-platform sql
1 Answers

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                                                                                    |
+-------------------+----------------------------------------------------------------------------------------------+
user_32872500
Related