Google Bigquery: Failed to parse input string "20170304" using PARSE_DATE Function

Problem 🔍

When converting a string to date value, if the right format is not specified or if there is bad data in the column, you will get this error message:

Error: Failed to parse input string "20170304"

Solution 💡

Verify the data and make sure you are using the right FORMAT Pattern

Step-1 Check the Sample data in the column that throws this error

SELECT employee_dob from employee;

Check the sample values. eg. If the format is "19831201", note it down

Step-2 Use the PARSE_DATE function to convert the string to date

Now lets use the Correct Format pattern to convert the string to date

In this case, since the date is in YYYYMMDD format, our function should look like this:

SELECT PARSE_DATE('%Y%m%d', employee_dob) as Parsed_DATE

If you string is in YYYY-MM-DD then,

SELECT PARSE_DATE('%Y-%m-%d', employee_dob) as Parsed_DATE

If your string is in DD/MM/YYYY then,

SELECT PARSE_DATE('%d/%m/%Y', employee_dob) as Parsed_DATE

More Formatting options:

• %Y - The year with century as a decimal number. eg: 2019
• %y - The year without century as a decimal number (00-99)
• %m - The month as a decimal number (01-12)
• %d - The day of the month as a decimal number (01-31)

Mike-Barn

posted on 22 Feb 19