Presto Or Trino, How to parse date with out separator

user_2458922 0
Viewed 194

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"

trino presto sql
1 Answers

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
user_25012790
Related