Snowflake changing varchar field to date - getting error 'Can't parse '1/7/2022' as date with format 'MM/DD/YYY'

If the year is formatted as YYY and all the dates are from year 2000 and after, you could pre-pend the year part with 2 to make it YYYY then use split_part to get just the date portion before casting it to date using try_to_date.

 select try_to_date(split_part(insert('01/31/021 0:00',7,0,'2'),' ',1)) 

You are missing a Y in your year. should be 4

I have used the TRY_ form of the parser to avoid the error message, but the result show NULL in snowflake, thus parsing fails.

The triple YYY form is only valid in the INTERVAL command, and this is not INTERVAL.

thus:

SELECT
    '1/7/2022' as d1,
    '01/07/2022' as d2,
    TRY_TO_DATE(d1, 'MM/DD/YYY'),    
    TRY_TO_DATE(d2, 'MM/DD/YYY'),
    TRY_TO_DATE(d1, 'MM/DD/YYYY'),    
    TRY_TO_DATE(d2, 'MM/DD/YYYY');

gives: