I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense. It's easy enough to do things like Xday between 1 and 31, but I still get an error when doing date(Xyear, Xmonth, Xday) if for example someone put in February 30th.

Is there some way I can either validate the date prior to doing the conversion to the date type or a way to just toss out the rows causing errors instead of throwing the exception?

  • temp = date(Xyear, 1, 1) -> temp = DATEADD(temp, INTERVAL Xmonth MONTH) -> temp = DATEADD(temp, INTERVAL Xday - 1 DAY) -> compare Xday and EXTRACT(DAY FROM temp), compare Xmonth and EXTRACT(MONTH FROM temp) - both must be equal. Combine this everything into one single expression.
    – Akina
    Dec 4 '20 at 17:46
  • Prefixing with SAFE. worked. Thanks!
    – Bobbie D
    Dec 4 '20 at 18:08
  • @BobbieD Added as answer so you can marked as accepted.
    – bbaird
    Dec 4 '20 at 18:14

You can add the prefix SAFE so the DATE function returns NULL instead of throwing an error.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.