How to convert a String to Date in BigQuery?
Contents
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.
Syntax : CAST AS DATE
|
1 |
CAST(expression AS DATE [format_clause]) |
- expression – This is the input value which can be any of the below data types
- STRING
- TIME
- DATETIME
- TIMESTAMP
- 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.
Examples
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.
|
1 2 |
SELECT CAST('2022/09/19' AS DATE FORMAT 'YYYY/MM/DD') AS string_to_date 2022-09-19 |
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.
|
1 2 |
SELECT CAST('22-09-19' as DATE FORMAT 'YY-MM-DD') AS string_to_date 2022-09-19 |
YYYY – 4 digits
|
1 2 |
SELECT CAST('2022-09-19' as DATE FORMAT 'YYYY-MM-DD') AS string_to_date 2022-09-19 |
YYY – last 3 digits
|
1 2 |
SELECT CAST('022-09-19' as DATE FORMAT 'YYY-MM-DD') AS string_to_date 2022-09-19 |
Y – last 1 digit
|
1 2 |
SELECT CAST('2-09-19' as DATE FORMAT 'Y-MM-DD') AS string_to_date 2022-09-19 |
Y,YYY – first digit and the remaining 3 digits
|
1 2 |
SELECT CAST('2,022-09-19' as DATE FORMAT 'Y,YYY-MM-DD') AS string_to_date 2022-09-19 |
RRRR
|
1 2 |
SELECT CAST('2022-09-19' as DATE FORMAT 'RRRR-MM-DD') AS string_to_date 2022-09-19 |
RR
It matches last 2 digits of the year. The behaviour of RR is different than the YY. Please refer GCP documentation.
|
1 2 |
SELECT CAST('22-09-19' as DATE FORMAT 'RR-MM-DD') AS string_to_date 2022-09-19 |
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.
|
1 2 |
SELECT CAST('09-19-2022' as DATE FORMAT 'MM-DD-YYYY') AS string_to_date 2022-09-19 |
MON – Matches 3 letters in month
|
1 2 |
SELECT CAST('SEP 19, 2022' as DATE FORMAT 'MON DD, YYYY') AS string_to_date 2022-09-19 |
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.
|
1 2 |
SELECT CAST('SEPTEMBER 19, 2022' as DATE FORMAT 'MONTH DD, YYYY') AS string_to_date 2022-09-19 |
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.
|
1 2 3 4 5 |
SELECT CAST('SEPTEMBER 19, 2022' AS DATE FORMAT 'MONTH DD, YYYY') AS string_to_date 2022-09-19 SELECT CAST('SEPTEMBER , 2022' AS DATE FORMAT 'MONTH , YYYY') AS string_to_date 2022-09-01 |
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.
|
1 2 |
SELECT CAST(TIMESTAMP'2022-09-19 06:00:31.499822 UTC' AS DATE) 2022-09-19 |
Recommended Articles
References from GCP official documentation
