I have a snowflake table column that has multiple date formats (timestamp with AM, PM, and just dates) stored as varchar.

Please let me know how to convert them to a timestamp_tz format using snowsql case statement or any other methods.

e.g. Different formats available in the column and their expected conversions are below

1. ('9/03/2020 12:00:00 AM' ,'mm/dd/yyyy hh12:mi:ss am') should be converted as 2020-03-09 12:00:00

2. ('9/03/2020 05:00:00 PM' ,'mm/dd/yyyy hh12:mi:ss pm') should be converted as2020-03-09 17:00:00

3. 13/09/2021 should be converted as2021-09-13 00:00:00

Thanks

CodePudding user response：

If you have a cascade of formatting options, use the TRY_TO_TIMESTAMP or TRY_TO_TIMESTAMP_NTZ so that failure results in NULL, and then chain them together with COALESCE

SELECT
    column1,
    TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh12:mi:ss am') as format1,
    TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh12:mi:ss pm') as format2,
    TRY_TO_TIMESTAMP(column1, 'dd/mm/yyyy') as format3, 
    COALESCE(format1, format2, format3) as date_a,
    COALESCE(TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh12:mi:ss am'),
             TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh12:mi:ss pm'),
             TRY_TO_TIMESTAMP(column1, 'dd/mm/yyyy')
        ) as date_b    
FROM VALUES
    ('9/03/2020 12:00:00 AM'),
    ('9/03/2020 05:00:00 PM'), 
    ('13/09/2021');

gives:

which shows format2 was not needed, so there is learnings, and compute savings!

CodePudding user response：

You just need to use the TRY_TO_TIMESTAMP function