Presto Or Trino, How to parse date with out separator

Question

Date is in format as YYYYMMDD eg 20200125= 20200 Jan 25. No Separator found in data. How to cast to date time as YY-MM-DD format

select date_parse( mydate, 'YYYYMMDD') as castDate 

SQL Error [7]: Query failed (#20220504_161754_00322_m75nu): Invalid format: "20200125"

date_parse( mydate, '%Y%m%d') as castDate

SQL Error [7]: Query failed (#20220504_161820_00323_m75nu): Invalid format: "MISSING"

Answer 1

select date_parse('20200125', '%Y%m%d') works fine for me:

Output:

_col0
2020-01-25 00:00:00.000

It seems that some dates are not in the provided format (i.e. not dates at all like "MISSING"). You can wrap your parse expression into try to see which ones:

select castDate
from table
where try(date_parse(castDate, '%Y%m%d')) is NULL
limit 5