Converting day datetime to timestamp in Snowflake

I have a datetime data in a table in Snowflake and I want to convert it into a timestamp

|-----------------------------------------|
|                Date                     |
|-----------------------------------------|
|      Wed 22 Mar 2022 12:51:21 -0500     |
|      Sun 28 Apr 2022 02:21:19 -0500     |
|      Mon 21 Mar 2021 18:31:59 -0500     |
|      Fri 12 Jan 2022 19:41:46 -0500     |
|      Thu 09 Feb 2022 23:51:17 -0500     |
|      Tue 17 May 2021 07:61:07 -0500     |
|      Wed 07 Oct 2022 01:71:01 -0500     |
|-----------------------------------------|

The output I want is:

|------------------------------------|
|                Date                |
|------------------------------------|
|      03/22/2022 12:51:21 -0500     |
|      04/28/2022 02:21:19 -0500     |
|      03/21/2021 18:31:59 -0500     |
|      01/12/2022 19:41:46 -0500     |
|      02/09/2022 23:51:17 -0500     |
|      05/17/2021 07:61:07 -0500     |
|      10/07/2022 01:71:01 -0500     |
|------------------------------------|

The methods I tried:

select to_date(date) from my_table

select to_date(date, 'mm/dd/yyyy h24:mi:ss') from my_table

select to_timestamp_tz(date) from my_table

etc.. None of the above conversions worked

Answer

using the correct formatting tokens, your valid datetime strings can be parsed. Depending if you what to have or not have timezone part on the timestamp, indicates which function you should use.

SELECT column1
    ,TRY_TO_TIMESTAMP_tz(column1, 'dy dd mon yyyy hh:mi:ss tzhtzm') as tz
    ,TRY_TO_TIMESTAMP(column1, 'dy dd mon yyyy hh:mi:ss tzhtzm') as default
    ,TRY_TO_TIMESTAMP_ntz(column1, 'dy dd mon yyyy hh:mi:ss tzhtzm') as ntz
FROM VALUES
    ('Wed 22 Mar 2022 12:51:21 -0500'),
    ('Sun 28 Apr 2022 02:21:19 -0500'),
    ('Mon 21 Mar 2021 18:31:59 -0500'),
    ('Fri 12 Jan 2022 19:41:46 -0500'),
    ('Thu 09 Feb 2022 23:51:17 -0500'),
    ('Tue 17 May 2021 07:61:07 -0500'),
    ('Thu 07 Oct 2022 01:71:01 -0500')

gives:

COLUMN1	TZ	DEFAULT	NTZ
Wed 22 Mar 2022 12:51:21 -0500	2022-03-22 12:51:21.000 -0500	2022-03-22 12:51:21.000	2022-03-22 12:51:21.000
Sun 28 Apr 2022 02:21:19 -0500	2022-04-28 02:21:19.000 -0500	2022-04-28 02:21:19.000	2022-04-28 02:21:19.000
Mon 21 Mar 2021 18:31:59 -0500	2021-03-21 18:31:59.000 -0500	2021-03-21 18:31:59.000	2021-03-21 18:31:59.000
Fri 12 Jan 2022 19:41:46 -0500	2022-01-12 19:41:46.000 -0500	2022-01-12 19:41:46.000	2022-01-12 19:41:46.000
Thu 09 Feb 2022 23:51:17 -0500	2022-02-09 23:51:17.000 -0500	2022-02-09 23:51:17.000	2022-02-09 23:51:17.000
Tue 17 May 2021 07:61:07 -0500	null	null	null
Thu 07 Oct 2022 01:71:01 -0500	null	null	null

because that last two are invalid times, if you correct the time to be in the valid range, the day being wrong is ignored.