Convert date timestamp(mm/dd/yy hh:mm) to date(mm/dd/yy) in snowflake

user_2960654 0
Viewed 120

I have loaded a csv file into snowflake and one of the date columns is in the format mm/dd/yy hh:mm (ex: 9/30/21 22:30). When I run this command

Select TO_VARCHAR(TO_DATE(operation_date,'DD/MM/YYYY hh:mm'),'MM/DD/YYYY')

I get this error:

Can't parse '9/30/21 22:30' as date with format 'DD/MM/YYYY hh:mm'

Can someone please tell me how I can fix this issue, so my final output would look something like: 09/30/21

snowflake-cloud-data-platform sql
1 Answers

the given string is 'MM/DD/YY hh24:mi' format, and the error message format should have been 'DD/MM/YYYY hh:mi'. When these are used together, and merged via COALESCE or NVL via a TRY_ function, you can merge the two types.

SELECT 
    column1 as operation_date,
    try_TO_DATE(operation_date,'DD/MM/YYYY hh:mi') as date_a,
    try_TO_DATE(operation_date,'MM/DD/YY hh24:mi') as date_b,
    nvl(date_a, date_b) as unified_date,
    TO_VARCHAR(unified_date,'MM/DD/YYYY')
from values
    ('30/9/2021 22:30'),
    ('9/30/21 22:30')
;

gives:

OPERATION_DATE DATE_A DATE_B UNIFIED_DATE TO_STRING
30/9/2021 22:30 2021-09-30 null 2021-09-30 09/30/2021
9/30/21 22:30 null 2021-09-30 2021-09-30 09/30/2021
user_439920
Related