Skip malformed date parsing presto

Question

I am using below query to parse date in presto:

SELECT date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p') from table t

Sample date is: 4/11/2021 12:30:00 PM

But sometime we get date which is not cannot be parsed like "testdate" (Any string which is not date)

How can I skip such dates in my query? My query should look like:

   select date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p') 
     from table t 
    where <skip the date that does not parse>

Answer 1

Use try(). Normally date_parse() fails on wrong date format. If you add try(), it will produce NULLs for wrong dates, you filter NULL records like this:

select try(date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p'))
  from table t 
  --filter rows wich can not be parsed if necessary
 where try(date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p')) is not NULL

Also you can try to parse different formats using coalesce() to pick successfully parsed:

select
      coalesce( try(date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p')), --try format1
                try(date_parse(t.up_date, '%Y/%m/%d'))  --try format2
              )
 from table t 
where --filter only parsed dates
     coalesce( try(date_parse(t.up_date, '%c/%e/%Y %l:%i:%s %p')), --try format1
               try(date_parse(t.up_date, '%Y/%m/%d'))  --try format2
              ) is not NULL;

In such way you can try parsing different formats which can be in your data.