Following date conversion query in Snowflake is not working

Question

A varchar column in a table contains dates in '05/13/2019 20:48:13 PM' format. It can contain either a single date or many dates concatenated with pipe |. I use this query to get the very first one and try to convert it to timestamp.

Select (CASE WHEN  charindex('AM', COMPL_DATE)>=1 OR   charindex('PM', COMPL_DATE) >= 1
        THEN TO_TIMESTAMP ( SPLIT_PART( COMPL_DATE, '|' , 1), 'MM/DD/YYYY hh24:mi:ss AM')
        ELSE TO_TIMESTAMP ( SPLIT_PART( COMPL_DATE, '|' , 1), 'MM/DD/YYYY hh:mi:ss')
     END 
    ) from some_table.

I get this error Can't parse '03/23/2019 20:56:22 PM' as timestamp with format 'MM/DD/YYYY hh24:mi:ss AM'.

It is not that it fails for all the rows. There are some rows for which it fails. However when I use this data to run separately, like this -

with tab as ( select  '03/23/2019 20:56:22 PM' COMPL_DATE from dual )
SELECT 
  (CASE WHEN  charindex('AM',COMPL_DATE)>=1 OR   charindex('PM',COMPL_DATE) >= 1
        THEN  TO_TIMESTAMP ( SPLIT_PART( COMPL_DATE, '|' , 1), 'MM/DD/YYYY hh:mi:ss AM')
        ELSE  TO_TIMESTAMP ( SPLIT_PART( COMPL_DATE, '|' , 1), 'MM/DD/YYYY hh24:mi:ss')
   END 
   ) result
FROM tab ;

It works fine.

Any help is appreciated.

and what happens if you use your second query on a row where it fails? (like find that row by some other ID value or sth). Because this might be a whitespace issue, so something the code does see but we don't — MMV
Please convert your column to datetime like this and try COMPL_DATE:: TIMESTAMP — Krishna

Gokhan Atil Gokhan Atil · Accepted Answer · 2019-12-23T09:30:54

It could be related with the extra space characters on your date string. I noticed that the web interface does not show the extra space characters in error messages so I'll contact with the development team to fix the space characters of the results in web interface.

Here's a sample query:

select  TO_TIMESTAMP ( '03/23/2019 20:56:22   PM', 'MM/DD/YYYY hh24:mi:ss AM');

The error message which you will see in the web interface:

Can't parse '03/23/2019 20:56:22 PM' as timestamp with format 'MM/DD/YYYY hh24:mi:ss AM'

If you run the same query on Snowflake CLI, you can see the error clearly:

PUBLIC>select  TO_TIMESTAMP ( '03/23/2019 20:56:22   PM', 'MM/DD/YYYY hh24:mi:ss AM');

100096 (22007): Can't parse '03/23/2019 20:56:22 PM' as timestamp with format 'MM/DD/YYYY hh24:mi:ss AM'

Could you verify the dates in your column?

Following date conversion query in Snowflake is not working

2 Answers