[SOLVED] Convert date text values from file to date time format in snowflake

Convert date text values from file to date time format in snowflake

LoadDate	Required Output
2021/07/30 00:00:00	2021-07-30 00:00:00
2020/03/06 00:00:00	2020-03-06 00:00:00
2024-May-23	2024-05-23 00:00:00
2020/06/26	2020-06-26 00:00:00

select TO_TIMESTAMP_NTZ(LOADDATE, 'YYYY/MM/DD HH:MI:SS');

but failing with the error

Can't parse '2024-May-23' as timestamp with format 'YYYY/MM/DD HH:MI:SS'

Could someone help me please

Solution

so long winded example code:

select $1 as input,
    $2 as wanted,
    try_to_timestamp_ntz($1,'YYYY/MM/DD hh24:mi:ss') as ts_1,
    try_to_timestamp_ntz($1,'YYYY-Mon-DD') as ts_2,
    try_to_timestamp_ntz($1,'YYYY/MM/DD') as ts_3,
    coalesce(ts_1, ts_2, ts_3) as _result,
    to_char(_result, 'yyyy-mm-dd hh24:mi:ss') = wanted as happy
    
from values
    ('2021/07/30 00:00:00', '2021-07-30 00:00:00'),
    ('2020/03/06 00:00:00', '2020-03-06 00:00:00'),
    ('2024-May-23', '2024-05-23 00:00:00'),
    ('2020/06/26',  '2020-06-26 00:00:00');