CAST function

[awilly]

Hello All,

I am wondering if something changed with the format clause for the CAST function in BQ as I have run the following statement in the past and it worked.  Now I get this error: 

Failed to parse input timestamp string at 5 with format element 'MM'

SELECT

CAST(col AS DATE FORMAT 'YYYY.MM.DD') AS alias1,

DATE_DIFF(CURRENT_DATE(), CAST(col AS DATE FORMAT 'YYYY.MM.DD'),DAY) AS alias2

FROM `table`

Thanks in advance

[mars124 · Accepted solution]

Nothing has changed for the CAST function syntax. The month portion of your date string is probably empty or exceeds 2 characters.

Better use SAFE_CAST if this is just a minor case in your data source.

[awilly]

Thanks @mars124, the SAFE_CAST function worked with no changes to my original query...I did have some bad data that I didnt realize was there. I appreciate your feedback.

[NorieRam]

Hi @awilly,

Welcome to Google Cloud Community!

In BigQuery, the CAST function does not support custom date formats; it is designed for straightforward type conversions and does not handle format strings. This is why you encountered an error with the FORMAT clause. For converting date strings with custom formats, use PARSE_DATE for date-only strings and PARSE_TIMESTAMP if your data includes time information. PARSE_DATE is preferred for simplicity when dealing with dates only.

Here’s how you can adjust your query:

SELECT
  PARSE_DATE('%Y.%m.%d', col) AS alias1,
  DATE_DIFF(CURRENT_DATE(), PARSE_DATE('%Y.%m.%d', col), DAY) AS alias2
FROM `table`

I hope this helps!

[awilly]

Hi @NorieRam,

Thank you for the warm welcome. Your adjusted query worked. I also filtered out some of the data as a test as well, so I definitely had some bad data triggering the errors in my initial query.  I appreciate your input.

[NorieRam]

Hi @awilly,

I’m glad the adjusted query worked for you. Thanks for the update!

[mars124]

@NorieRam What did you mean by "the CAST function does not support custom date formats"? This example shows that it does:

[NorieRam]

Hi @mars124,

You’re correct that the example shows how CAST can format a date string. However, CAST is generally not recommended for handling custom date formats in BigQuery. CAST is designed for basic type conversions. Using it for custom formats can lead to unpredictable results or errors.

[mars124]

Both CAST [with format clause] and PARSE_DATE can lead to errors when the source data is not uniformly formatted, which is most likely the case of the original post.

The main advantage when using PARSE_DATE is the specificity of the error message. It will provide a more detailed error message.

CAST error message: "Failed to parse input timestamp string at 5 with format element 'MM'"
PARSE_DATE error message: "Failed to parse input string "2024.009.16""

Using CAST for custom formats can lead to less specific error messages, if any.