When working with data, situations will arise where data will need to be converted from one type to another. For instance, an analyst might receive date in a format that BigQuery does not support. When loaded, BigQuery will automatically detect the type as a string. If the analyst wants to do any type of date arithmetic, they will need to convert the string to a date.
The following is a list of all types that can be cast into other types:
- Casting can be achieved using the CAST() function:
CAST(<EXPRESSION> AS NEWTYPE)
For instance, if I want to cast a Boolean into an integer: CAST(X AS INT64).
- Strings of the format YYYY-DD-MM can be cast to Date, Datetime, or Timestamp: CAST(X AS DATE). In the case of casting to Datetime or Timestamp, the time will be set to midnight.
For instance, CAST('2017-01-01' AS TIMESTAMP) would be cast to 2017-01-01 00:00:00 UTC.
Cast alternatives:
- CURRENT_DATE(): Returns the current date in date format.
- EXTRACT: The extract function allows an analyst to return only part of a date in integer format. The returned integer values can then be concatenated to create complex date formats, unioned with text values or used in date arithmetic.
For instance, to return the week of the current date: EXTRACT(WEEK FROM CURRENT_DATE()).
EXTRACT(DAY FROM '2017-01-01') returns 01, while EXTRACT(YEAR FROM '2017-01-01') returns 2017.
The PARSE_DATE() function can be used in Standard SQL to convert unrecognized string dates to recognized date formats.
PARSE_DATE('%d/%m/%Y','23/03/2015') returns 2015-03-23, which is a recognized date format.