I have a column of VARCHAR format that comes in as 'MM/DD/YYY 0:00'. I've done a replace 'DATEFROM = REPLACE(DATEFROM,' 0:00','') ' to get rid of the useless time portion.

Then I was trying 'TO_DATE(DATEFROM::VARCHAR, 'MM/DD/YYY')' but I get the error 'Can't parse '1/7/2022' as date with format 'MM/DD/YYY''.

I've tried without the ::VARCHAR which gives the same error. I've also tried TRY_TO_DATE which put null into the field.

I've also tried a Trim on the DATEFROM column with the same error occurring.

While I'd like something that would take the 'MM/DD/YYY 0:00' format and allow me to get to 'YYYY-MM_DD' but I think that might be a stretch.

CodePudding user response：

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)) 

CodePudding user response：

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: