CAST
Definition
The CAST function allows you to convert between different Data Types in BigQuery.
Where typename is any of:
CAST (expr AS typename)INT64,NUMERIC,BIGNUMERIC,FLOAT64,BOOL,STRING,BYTES,DATE,DATETIME,TIME,TIMESTAMP,ARRAY,STRUCT
You can read more about each type here.
| str_number | number |
|---|---|
| 3 | 3 |
| 8 | 8 |
| 23 | 23 |
Practical Info
Converting a STRINGs to DATEs
When converting a STRING to DATE, the STRING must be in the format: YYYY-MM-DD
| date1 | date2 | formatted_date2 |
|---|---|---|
| null | 2020-02-01 | Feb 01,2020 |
In the example above date1 returns a NULL value since it's not in the right format. To re-format your strings check out the page.
Similar rules apply for converting STRINGs to DATETIME, TIMESTAMP, and TIME:
When casting STRING -> DATETIME, the string must be in the format YYYY:MM:DD HH:MM:SS
| str_to_datetime |
|---|
| 2020-12-25T03:22:01 |
When casting STRING -> TIMESTAMP the string must be in a format YYYY:MM:DD HH:MM:SS [timestamp] (where the timestamp defaults to UTC if not specified)
| str_to_timestamp |
|---|
| 2020-12-25T08:22:01.000Z |
When casting STRING->TIME the string must be in the format HH:MM:SS
| str_to_time |
|---|
| 15:03:11 |
Converting to BOOLs
When converting to BOOLs (Booleans, True/False) from other types there are a few rules to keep in mind:
INT64 -> BOOL: Returns FALSE for 0, TRUE otherwise.
| zero_to_bool | five_to_bool |
|---|---|
| false | true |
STRING -> BOOL: Returns TRUE for 'true', FALSE for 'false', NULL otherwise.
| true_to_bool | false_to_bool | apples_to_bool |
|---|---|---|
| true | false | null |
Common Questions
How to convert a STRING to a DATE?
Let's say you have a stringifed date that's not formatted in the standard YYYY-MM-DD, how do you cast it to be a date?
There are 2 options:
Use PARSE_DATE where you can specify the exact syntax of your string:
| f0_ |
|---|
| 2020-01-03 |
Use STRING manipulation to reconstruct your string and CAST to the right format:
SELECT
PARSE_DATE('%m/%d/%Y', '1/3/2020')| date_from_str |
|---|
| 2020-12-03 |
How to convert EPOCH to TIMESTAMP?
Epochs, or Unix time are the seconds since 1970-01-01 00:00:00 UTC. To make them usable for analysis, you'll need to convert them to a DATETIME or TIMESTAMP.
To do this, you can again make use of PARSE_DATETIME, or PARSE_TIMESTAMP if your epoch is a STRING.
| parse_timestamp_s_1611592773 |
|---|
| 2021-01-25T16:39:33.000Z |
Or if your Epoch is an INT64, use the following functions: TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS, UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS.
SELECT parse_timestamp('%s', '1611592773')| formatted_timestamp |
|---|
| 2021-01-25T16:39:33.000Z |
How to convert scientific notation to decimal format?
SELECT
TIMESTAMP_SECONDS(1611592773) AS formatted_timestampCAST to NUMERIC to turn a Scientific Notation column to a decimal:
| sci | sci_numeric |
|---|---|
| 32000000.01 | 32000000.010000002 |
Troubleshooting Common Errors
Could not cast literal to type DATE
Check the format of your string when converting to a DATE. Remember it must be in YYYY-MM-DD . But also try PARSE_DATE if you're looking for something to read a specific format.
Related Pages
- Dates and Times in Standard SQL