2

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>
leftjoin
  • 36,950
  • 8
  • 57
  • 116
Hardik
  • 31
  • 1
  • 4

1 Answers1

4

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • 1
    Sometimes one can put a where clause to skip it, but then it wouldn't have an output row. Depending on your intent, this may or may not be acceptable. If there are more than one table involved, could also try putting clause in the "ON" condition for the join. – JosephDoggie May 05 '21 at 13:08