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

CodePudding user response：

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:

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