Getting: INVALID_FUNCTION_ARGUMENT: Invalid format: "" when trying to date

Query:

SELECT date_parse(start_date, '%Y/%m/%d')
FROM sql_question_five_2 ;

date format looks like this in csv: 20210531 being read into table as string.

Have tried a few different things to get it to convert to a date YYYY-MM-DD

CodePudding user response：

Your format string needs to match the source value. Your source doesn’t have /‘s in it so your format string shouldn’t either

CodePudding user response：

date_parse expects all inputs to look like the format string, if they don't it will throw an error. You can do something like this to avoid the problem:

SELECT IF(start_date = '', NULL, date_parse(start_date, '%Y/%m/%d'))
FROM sql_question_five_2

This guards against the case where the string is empty, which is the case when you get the error. If you have other strings that don't conform to the format you would have to guard against those too.

If that is the case you can use the TRY function which captures errors and returns NULL:

SELECT TRY(date_parse(start_date, '%Y/%m/%d'))
FROM sql_question_five_2