Failed to parse input string in BigQuery with parse_date

Question

I have a column 'appointment_date' in string formate representing a date dd.mm.yyyy.

In BiqQuery I am using the following query to find all appointments dates lying in the future:

SELECT appointment_date
FROM `appointments` 
where parse_date('%d.%m.%Y', appointment_date) > current_date()

BiqQuery returns the following error message: Failed to parse input string ""

Please advice.

Thanks, Janine

Answer 1

Use safe.parse() to avoid the error:

where safe.parse_date('%d.%m.%Y', appointment_date) > current_date()

This will return NULL for invalid formats rather than an error.