How to convert a String to Date in BigQuery?

CAST function in BigQuery

Cast is a conversion function which is used to convert the given data type to different data type. BigQuery supports varies data type conversions using CAST. Example: CAST AS ARRAY, CAST AS BIGNUMERIC, CAST AS BOOL, CAST AS BYTES, CAST AS DATE etc.

If the cast is not possible for the given values, it throws an error. To avoid these error, SAFE_CAST function can be used. SAFE_CAST returns NULL if BigQuery is unable to cast the data type. In this tutorial, we will use the CAST AS DATE for converting the string values to DATE.


  • expression – This is the input value which can be any of the below data types
    • STRING
    • TIME
  • AS DATE – It is a keyword which denotes the target data type. Here DATE is a target data type.
  • format_clause – It is an optional parameter. It defines the format of the given date values such as YYYY-MM-DD, YYYY/MM/DD and so on. If the expression is a STRING value, it can be used to defines the format.


Cast String to Date in BigQuery

In this example, the date value is given as string 2022/09/19. The corresponding date format(YYYY/MM/DD) is mentioned using FORMAT clause. The cast function checks the string value as per the given date format. Then it converts the given string value to Date.

Year Parts : Cast String with YY-MM-DD to Date

In the CAST function, the year value can be mentioned with 2 digits. For example: 22-09-19 (YY-MM-DD). While it converting the string to date, the missing part of the year (first 2 digits) is taken from the current date. When we write this article, the current date is 2022-09-19. So the cast function taken the first 2 digits (20) from the year value.

YYYY – 4 digits

YYY – last 3 digits

Y – last 1 digit

Y,YYY – first digit and the remaining 3 digits



It matches last 2 digits of the year. The behaviour of RR is different than the YY. Please refer GCP documentation.

Month Parts : Cast String with MM-DD-YYYY to Date

In this example, we focus on the month value in string format. Here we have given the string value with format (MM-DD-YYYY). Since the string value is matched with given Date format, CAST function converts the String to Date and returns the value in Date datatype.

MON – Matches 3 letters in month

MONTH – Matches 9 letters in month

The format MONTH can be used for any month. But the complete month name should be mentioned in the string value. Otherwise it throws parse error.

Day Parts in Cast as Date

The day part is mentioned with format DD. If it is missing in the string value, CAST function returns first day of the given month.

Cast Timestamp to Date in BigQuery

In this example, we are giving the timestamp value in string format. Using CAST as Date expression, we are converting timestamp value to Date.

Recommended Articles

References from GCP official documentation