how to parse and convert 'Jan 15 2019 6:57PM' to timestamp format in snowflake

Question

How can I convert a string in the format 'Jan 15 2019 6:57PM' to a proper timestamp format in snowflake.

It is throwing error as cannot parse timestamp 'Jan 15 2019 6:57PM' to TIMESTAMP format with below command.

select 'Jan 15 2019 6:57PM'::TIMESTAMP;

Is there any way to do this? The date and time can be either single or double digit and even time of the day can be either AM or PM.

Answer 1

Can you try this one?

select to_timestamp('Jan 15 2019 6:57PM','MON DD YYYY HH12:MIAM');

+------------------------------------------------------------+
| TO_TIMESTAMP('JAN 15 2019 6:57PM','MON DD YYYY HH12:MIAM') |
+------------------------------------------------------------+
| 2019-01-15 18:57:00.000                                    |
+------------------------------------------------------------+