BigQuery Conversion Functions: A 2025 Guide to Data Casting

Ievgen Krasovytskyi

Head of Marketing

April 8, 2024

In today's data-driven landscape, seamlessly transforming data types is essential for optimizing analytics workflows and ensuring data accuracy and consistency. Conversion functions in BigQuery are designed to transform data from one type to another, facilitating data manipulation and analysis by ensuring compatibility between different data types within queries.

BigQuery offers advanced conversion functions like CAST and SAFE_CAST, designed to handle these transformations efficiently. Whether you're converting dates to strings for reporting, integers to floats for calculations, or any other data type conversion, BigQuery's functionalities are indispensable tools for data engineers, analysts, and business intelligence professionals.

‍

This guide aims to enhance your data analytics capabilities by providing a deep dive into these conversion functions, empowering you to make the most of your data in 2024.

The Power of Conversion Functions in BigQuery

The official Google documentation details BigQuery's conversion functions, which encompass a broad spectrum of capabilities designed to convert data from one type to another seamlessly. The documentation provides comprehensive insights into each function, including CAST, SAFE_CAST, PARSE_DATE, FORMAT_DATE, and others, illustrating their syntax, usage, and the nuances of data type compatibility.

‍

Leveraging these functions not only streamlines the data transformation process but also empowers professionals to harness the full potential of their data, unlocking advanced insights and driving informed decision-making. It's a critical skill set for anyone looking to excel in data-intensive roles and leverage BigQuery's advanced analytics capabilities to their fullest.

The List of Conversion Functions

BigQuery's array of conversion functions enables sophisticated data manipulation by allowing various data types to be transformed with precision. Here's a breakdown of some key functions and their capabilities:

‍

CAST: The cornerstone of data type conversion in BigQuery, CAST is used to change one data type into another, such as converting a string to an INTEGER or a date to a DATETIME. This function is fundamental for adjusting data formats to meet analytical needs.

‍

SAFE_CAST: Similar to CAST, but with a safety net. SAFE_CAST attempts to convert a data type into another, and if the conversion is not possible, it returns NULL instead of causing an error. This function is crucial for maintaining data integrity when uncertain about data format consistency.

‍

FORMAT: This function is used to format data types into string representations, following specified patterns. FORMAT can be particularly useful for presenting numeric or DATE data in a more readable or standardized format for reports.

‍

PARSE: Focused on converting string data into more structured data types like DATE, DATETIME, or NUMERIC, this function is essential for extracting and converting textual data into a specific format or structure.

‍

These functions collectively enhance BigQuery's data processing capabilities, allowing users to perform complex data transformations and manipulations efficiently.

Diving Deep into BigQuery CAST Function

The BigQuery CAST function is a powerful tool that allows for the conversion of data from one type to another, facilitating seamless data manipulation and analysis. Mastery of the CAST function is essential for professionals working with BigQuery to ensure data integrity and unlock the full potential of their data sets.

Essential Syntax for Efficient Data Casting

The syntax for the CAST function in BigQuery is straightforward yet flexible, allowing for a wide range of data type conversions. The basic form is:

‍

CAST(expression AS data_type)

‍

Here:

expression: It represents the value or column name you wish to convert.
data_type: It specifies the target data type for the conversion.

‍

Best Practices for Using CAST in BigQuery

‍

Understand Data Type Compatibility: Not all data types can be seamlessly converted to others. Familiarize yourself with BigQuery's data type compatibility to avoid runtime errors.
Use SAFE_CAST for Error Handling: When uncertain about the success of a conversion, use SAFE_CAST instead of CAST. SAFE_CAST returns NULL instead of throwing an error if the conversion fails, preventing your query from failing.
Explicitly Handle NULL Values: Before casting, consider how NULL values should be handled in your data set. Use functions like IFNULL or COALESCE to provide default values if necessary.
Performance Considerations: While CAST is efficient, excessive casting, especially in large datasets, can impact query performance. Aim to store data in the desired type from the outset to minimize the need for runtime conversions.

Examples of CAST Function in Action

To illustrate the versatility of the CAST function, let's explore a few practical examples:

‍

Converting a string to a date:

SELECT CAST('2024-01-01' AS DATE) as NewDate;

‍

‍

This conversion is crucial for data analysts who need to manipulate and analyze time-series data, ensuring DATES are in the correct format for comparison and aggregation.

‍

Converting an integer to a float:

SELECT CAST(employee_id AS FLOAT64) as EmployeeID_Float;

‍

This example demonstrates the conversion of an integer to a float, enabling precise calculations that require decimal values.

Transforming strings to timestamps:

SELECT CAST('2024-02-14 08:30:00' AS TIMESTAMP) as ValentineMorning;

‍

‍

Converting strings to timestamps is vital for logging events or transactions with precise time records.

‍

Common use cases for CAST in BigQuery:

Data Normalization: Standardizing data formats across tables and databases, such as converting all dates to a uniform format.
Data Integration: Harmonizing data from different sources, requiring conversions between compatible but different data types.
Analytical Transformations: Preparing data for analysis by converting data types to match the requirements of statistical functions or models.

Comprehensive Guide to BigQuery CAST Functions

This section aims to dive deeper into the specifics of how the CAST function can be utilized across various data types in Google BigQuery, offering a detailed examination of syntax, conversion rules, and practical examples for each type.

Google BigQuery CAST AS ARRAY

Google BigQuery's CAST AS ARRAY function is a versatile tool that converts data into arrays of a specified type. This function is particularly useful to ensure that data elements are consistently formatted for array operations, such as aggregation or comparison across multiple data points.

‍

Syntax:

CAST(expression AS ARRAY<data_type>)

‍

Here:

expression: The data or column you wish to convert into an array.
ARRAY<data_type>: The target array data type. Specifies the type of elements the resulting array will contain.

‍

Rules for conversion:

The expression being converted must be compatible with the specified data_type in the array.
Only expressions that can logically be grouped into an array are valid for conversion.

‍

Example:

Convert a string representation of numbers into an array of integers for further numerical analysis.

‍

SELECT CAST('[1, 2, 3]' AS ARRAY<INT64>) AS NumberArray;

‍

The string '[1, 2, 3]' is being converted into an array of integers (INT64).
The result, NumberArray, will be an array containing the integers 1, 2, and 3, allowing for numerical operations on these elements as part of an array.

‍

‍

This example demonstrates the power of CAST AS ARRAY in BigQuery, enabling complex data transformations that facilitate advanced data analysis and manipulation techniques.

Google BigQuery CAST AS BIGNUMERIC

BIGNUMERIC in Google BigQuery is a data type designed for storing very large numbers with high precision, making it ideal for financial calculations, scientific data, and any other use case requiring extensive numerical precision. Using the CAST function to convert data to BIGNUMERIC ensures that your calculations retain their accuracy without the risk of overflow or loss of detail.

‍

Syntax:

CAST(expression AS BIGNUMERIC)

‍

Here:

expression: It's the value or field you wish to convert into a BIGNUMERIC type.

‍

Rules for conversion:

The source data must represent a numeric value that fits within the BIGNUMERIC range.
BIGNUMERIC can support up to 76.76 decimal digits of precision, with a scale (number of digits to the right of the decimal point) of up to 38. Values exceeding this precision may be rounded.

‍

Example:

To demonstrate the conversion of a large floating-point number into BIGNUMERIC for precise financial calculations.

‍

SELECT CAST(123456789.12345678901234567890123456789012345678 AS BIGNUMERIC) AS BigNumericValue;

‍

‍

This query converts a highly precise floating-point number into a BIGNUMERIC type. The goal is to ensure that all decimal places are retained without rounding off, which is critical in scenarios where high precision is necessary, such as financial transactions or scientific measurements. The CAST function facilitates this conversion, ensuring the original value's precision is maintained in the BIGNUMERIC format.

Google BigQuery CAST AS BOOL

The CAST AS BOOL function in Google BigQuery is a versatile tool that converts various data types into Boolean values (TRUE or FALSE). It is handy for transforming numeric and string representations into Boolean logic, facilitating condition-based analysis and decision-making processes in SQL queries.

‍

Syntax:

CAST(expression AS BOOL)

‍

Here:

expression: It is the data (string, numeric, etc.) you wish to convert into a Boolean value.

‍

Rules for conversion:

Numeric values are converted to TRUE (for non-zero values) or FALSE (for zero).
String values that are case-insensitive matches for "true" are converted to TRUE, and matches for "false" are converted to FALSE.
Any other strings or incompatible types will result in an error if not used with SAFE_CAST.

‍

Example:

Determine whether customers have an active subscription based on a database column that stores "1" for active and "0" for inactive subscriptions.

‍

SELECT customer_id, CAST(subscription_status AS BOOL) AS is_active FROM customer_subscriptions;

‍

‍

In this query, subscription_status is assumed to be a numeric column, where 1 represents an active subscription and 0 represents no subscription. By casting subscription_status to a Boolean, the query transforms these numeric values into TRUE (active) or FALSE (inactive), making the data more intuitive for subsequent analysis or reporting.

Google BigQuery CAST AS BYTES

The CAST AS BYTES function in Google BigQuery is crucial for converting string values into byte arrays. This functionality is handy in scenarios involving data encryption, hashing, or any situation requiring binary data representation. Converting strings to bytes can also be essential for data compression or when interacting with binary protocols.

‍

Syntax:

CAST(expression AS BYTES)

‍

Here:

expression: It is the string expression that you want to convert into bytes.

‍

Rules for conversion:

The input expression must be a string. Non-string inputs will result in an error unless they can be implicitly converted to a string before being cast to bytes.
Encoding considerations are essential. The string is converted to bytes using its UTF-8 representation. Therefore, understanding the character encoding of your data is crucial.

‍

Example:

Convert a simple greeting string into its byte representation for encryption purposes.

‍

SELECT CAST('Hello, World!' AS BYTES) as BytesRepresentation;

‍

‍

This example demonstrates how to convert the string 'Hello, World!' into a byte array using the CAST AS BYTES function in BigQuery.
The output will be the UTF-8 byte representation of the string, suitable for encryption or other binary data processing tasks.
Understanding the output requires familiarity with binary or hexadecimal representations, as the result is not readily human-readable.

‍

By leveraging the CAST AS BYTES function, BigQuery users can efficiently transition between string data and a byte-based representation, enabling advanced data processing capabilities.

Google BigQuery CAST AS DATE

Converting data to a DATE format is common in data processing, mainly when dealing with time-series data, historical records, or any scenario where date-specific analysis is crucial. The CAST AS DATE function in Google BigQuery transforms compatible data types into a DATE format, enabling precise and straightforward temporal analysis.

‍

Syntax:

CAST(expression AS DATE)

‍

Here:

expression: This is the value or field you wish to convert into a DATE. It can be a string representing a DATE, a TIMESTAMP, or even a DATETIME value.
AS DATE: This part of the syntax specifies that the conversion target is a DATE data type.

‍

Rules for conversion:

The expression being converted must be in a recognizable DATE format, such as 'YYYY-MM-DD', or be a valid TIMESTAMP or DATETIME from which the time component will be discarded.
If the expression is a string that does not match a valid DATE format, the conversion will fail, potentially resulting in an error unless SAFE_CAST is used.

‍

Example:

Convert a string representing a specific date into a DATE data type to filter records for events that occurred on New Year's Day, 2024.

‍

SELECT CAST('2024-01-01' AS DATE) as NewYearDate;

‍

‍

In this example, the string '2024-01-01' is converted to a DATE data type, making it easier to perform date-specific queries on the dataset. The conversion enables date comparison operators and functions within BigQuery to efficiently manipulate or filter data based on date criteria.

‍

The CAST function transforms the string '2024-01-01' into a date, allowing for direct comparison, sorting, and aggregation by date within your SQL queries. This conversion is especially useful in scenarios where the original data type might not have been conducive to such operations, ensuring data uniformity and facilitating temporal analysis.

Google BigQuery CAST AS DATETIME

The CAST AS DATETIME function in Google BigQuery is a versatile tool that converts expressions from various data types, such as STRING or TIMESTAMP, into the DATETIME format. This function is especially useful for data analysis and reporting where precise date and time representations are required without the time zone specificity that comes with the TIMESTAMP data type.

‍

Syntax:

CAST(expression AS DATETIME)

‍

Here:

expression: The value or column name you wish to convert. This can be a STRING, DATE, or TIMESTAMP.
AS DATETIME: Specifies the target data type for the conversion, which in this case is DATETIME.

‍

Rules for conversion:

The expression being converted must be in a format recognizable as a DATE or DATETIME. For strings, this typically means formats like YYYY-[M]M-[D]D[([T]|[ ])HH:MM:SS[.SSSSSS]].
If the expression is a DATE, the resulting DATETIME will have a time component of 00:00:00.
When converting from TIMESTAMP, the conversion retains the date and time components but discards the time zone information.

‍

Example:

Convert a string representing a date and time into the DATETIME data type to perform datetime-specific operations without considering time zone effects.

‍

SELECT CAST('2024-01-01 12:00:00' AS DATETIME) as NewYearNoon;

‍

‍

'2024-01-01 12:00:00': The string expression representing a specific date and time on New Year's Day at noon.
AS DATETIME: Indicates that the string should be converted into a DATETIME data type.
NewYearNoon: The alias for the resulting DATETIME value, which can be used in subsequent parts of a query for further analysis or operations.

‍

This example demonstrates how to convert a textual representation of a date and time into a DATETIME value in BigQuery, enabling precise and timezone-agnostic datetime manipulations in data analysis workflows.

Google BigQuery CAST AS FLOAT64

The CAST AS FLOAT64 function in Google BigQuery is a powerful tool for converting data into floating-point numbers, which are essential for precise calculations and numerical analysis. This function is best utilized when working with data that requires decimal precision, such as financial figures, measurements, or scientific calculations.

‍

Syntax:

CAST(expression AS FLOAT64)

‍

Here:

expression: The data you want to convert to a floating-point number. This can be a column name, a literal value, or any expression that evaluates to a numeric value or a string representing a numeric value.

‍

Rules for conversion:

Numeric expressions, including integers and strings representing a number, can be converted to FLOAT64.
The expression should not contain any characters other than numeric characters, decimal points, or leading/trailing whitespaces. Commas as thousand separators are not supported and will cause an error.
If the expression is a string that does not represent a valid floating-point number, the query will result in an error unless SAFE_CAST is used.

‍

Example:

To demonstrate the conversion of a string representing a numeric value with decimal points into a FLOAT64 type to perform precise numerical calculations.

‍

SELECT CAST('123.456' AS FLOAT64) as ConvertedFloat;

‍

‍

'123.456': The string representing the numeric value to be converted.
AS FLOAT64: Specifies the target data type for the conversion.
ConvertedFloat: The resulting floating-point number from the conversion. In this example, the string '123.456' is converted into a floating-point number 123.456, enabling precise numerical operations on this value.

‍

This conversion is particularly useful in scenarios where the precision of numerical data is crucial, such as financial analyses, scientific computations, or any application requiring high numerical precision.

Google BigQuery CAST AS INT64

The CAST AS INT64 function in BigQuery is designed to convert various data types into an integer (INT64) format. This is particularly useful for performing mathematical operations, setting primary keys, or performing precise numeric operations anywhere without fractional components.

‍

Syntax:

CAST(expression AS INT64)

‍

Here:

expression: The data you want to convert to INT64. It can be a number, a boolean, or a string representing an integer.

‍

Rules for conversion:

Numeric values (both float and integer) are directly convertible to INT64.
True (boolean) converts to 1, and False (boolean) converts to 0.
Strings must represent valid integer values, or the conversion will fail.

‍

Example:

Convert a string representing a numeric value into an INT64 to use as an ID.

‍

SELECT CAST('123456' AS INT64) as UserID;

‍

‍

This example demonstrates converting a string '123456' into an integer value of 123456, which can then be used as a numeric identifier in your dataset.

Google BigQuery CAST AS INTERVAL

The CAST AS INTERVAL function in BigQuery converts compatible data types into INTERVAL, representing a time duration. This function is invaluable for calculating differences between timestamps or scheduling future events relative to current DATES.

‍

Syntax:

CAST(expression AS INTERVAL)

‍

Here:

expression: The data you want to convert to INTERVAL. Typically, this will be a STRING or NUMERIC type that specifies the duration.

Rules for conversion:

The expression must clearly represent a time interval in a format that BigQuery recognizes, such as a string specifying the duration ("1-2" for 1 year 2 months).
To accurately define a time interval using the CAST function in BigQuery, the input string must adhere to the specific format 'Y-M D H:M:S.F', where each component represents years, months, days, hours, minutes, seconds, and fractional seconds respectively.

‍

Example:

Define a time interval of 30 days using a string

SELECT CAST('30 00:00:00' AS INTERVAL) AS ThirtyDays;

‍

‍

This example shows how to define a 30-day interval in BigQuery using CAST('30 00:00:00' AS INTERVAL), where '30' represents days and time components are set to zero. This format 'Y-M D H:M:S.F' is mandatory for intervals, allowing for precise duration operations on DATE or TIMESTAMP values. Proper formatting is crucial to prevent errors in interval expressions.

Google BigQuery CAST AS Numeric

The CAST AS NUMERIC function in BigQuery is used to convert data into the NUMERIC type, which is suitable for high-precision arithmetic operations. It's especially useful in financial calculations where precision up to 38 decimal digits is required.

‍

Syntax:

CAST(expression AS NUMERIC)

‍

Here:

expression: The data you want to convert to NUMERIC. This can be a string, integer, or floating-point number.

‍

Rules for conversion:

Strings must represent valid numeric values in a format recognizable as NUMERIC.
Floating-point and integer values are directly convertible to NUMERIC.

‍

Example:

Convert a floating-point value to NUMERIC for precise financial calculations.

‍

SELECT CAST(123456.789 AS NUMERIC) as PreciseAmount;

‍

‍

This example demonstrates converting a floating-point number 123456.789 into a NUMERIC value, ensuring precision for subsequent financial analysis or calculations.

Google BigQuery CAST AS STRING

Casting to STRING in BigQuery is versatile, allowing virtually any data type to be converted into a text format. This function is especially useful for concatenating text, making data human-readable, or preparing for exports that require string formats.

‍

Syntax:

CAST(expression AS STRING)

‍

Here:

expression: The data you want to convert to a string.

‍

Rules for conversion:

Most data types can be seamlessly converted to strings.

‍

Example:

Here’s how to convert an integer to a string to concatenate it with text.

‍

SELECT CONCAT('Employee ID: ', CAST(employee_id AS STRING)) as EmployeeIDString FROM your_table;

‍

‍

This query converts the employee_id field from an integer to a string and concatenates it with the text 'Employee ID: ', resulting in a readable string format for each row.

Google BigQuery CAST AS STRUCT

The CAST AS STRUCT function in BigQuery allows converting expressions into STRUCT data types. STRUCTs are helpful for creating nested data structures within a single column, facilitating complex data manipulation and analysis.

‍

Syntax:

‍

CAST(expression AS STRUCT<field_name data_type[,...]>)

Here:

expression: The data or expression you're converting.
field_name data_type: Defines the names and types of fields within the STRUCT.

Rules for conversion:

The expression must match the STRUCT definition regarding data types and structure.

‍

Example:

Here’s how to convert a set of columns into a STRUCT for nested querying.

‍

SELECT CAST((name, age) AS STRUCT<name STRING, age INT64>) as Person FROM employees;

‍

‍

This example converts two columns, name, and age, into a single STRUCT called Person, allowing for more structured data analysis.

Google BigQuery CAST AS TIME

CAST AS TIME in BigQuery converts expressions to the TIME data type. This is particularly useful for operations that require only the time portion of a DATETIME or TIMESTAMP, such as scheduling and time-based calculations.

‍

Syntax:

CAST(expression AS TIME)

‍

Here:

expression: The data you want to convert to time.

‍

Rules for conversion:

The expression must be in a valid time format or a part of a TIMESTAMP or DATETIME that can be isolated as time.

‍

Example:

Here’s how to extract the time portion from a timestamp.

‍

SELECT CAST(timestamp_field AS TIME) as TimeOnly FROM your_table;

‍

‍

This query takes a timestamp_field and extracts only the time portion, ignoring the date part, which is useful for time-specific analysis.

Google BigQuery CAST AS TIMESTAMP

Casting to TIMESTAMP is essential for converting various date and time expressions into a unified TIMESTAMP format in BigQuery. This enables precise time-based calculations, comparisons, and aggregations.

‍

Syntax:

CAST(expression AS TIMESTAMP)

‍

Here:

expression: The data you want to convert to a TIMESTAMP.

‍

Rules for conversion:

The expression must be convertible to a TIMESTAMP, such as a string in a recognized TIMESTAMP format or a DATE/DATETIME value.

‍

Example:

Here’s how to convert a date and time string to a TIMESTAMP.

‍

SELECT CAST('2024-01-01 12:00:00' AS TIMESTAMP) as NewYearNoon FROM your_table;

‍

‍

This query converts a string representing a specific date and time into a TIMESTAMP data type, allowing for precise time-based operations and analysis.

Google BigQuery PARSE_BIGNUMERIC

PARSE_BIGNUMERIC function is designed for converting string representations of numeric values into BIGNUMERIC data type in BigQuery. This function is best used when dealing with extremely large or precise numerical values that exceed the limits of standard numeric types.

‍

Syntax:

PARSE_BIGNUMERIC(expression, [safe_error_mode])

‍

Here:

expression: The string representation of the numeric value to be converted.
safe_error_mode (optional): A boolean that specifies whether to return NULL instead of an error if the conversion fails.

‍

Rules for conversion:

The string must represent a valid numeric value that fits within the BigNumeric type's range.
Optional scientific notation (e.g., "1e10") is supported.
Leading and trailing spaces are ignored.

Example:

Here’s how to convert a string representing a large number into BIGNUMERIC.

‍

SELECT PARSE_BIGNUMERIC('123456789.123456789123456789') as BigNumericValue;

‍

‍

This example demonstrates converting a high-precision numeric string into a BigNumeric data type, enabling operations on very large or precise numbers.

Google BigQuery PARSE_NUMERIC

The PARSE_NUMERIC function converts string representations of numeric values into the numeric data type. It is ideal for converting strings that represent precise decimal values up to 38 digits of precision, fitting within the Numeric data type's limits.

‍

Syntax:

PARSE_NUMERIC(expression, [safe_error_mode])

‍

Here:

expression: The string representation of the numeric value to be converted.
safe_error_mode (optional): A boolean that specifies whether to return NULL instead of an error if the conversion fails.

‍

Rules for conversion:

The string must accurately represent a numeric value within the Numeric type's precision and scale limits.
Scientific notation is accepted.
Spaces before and after the numeric value are trimmed.

‍

Example:

Here is how to convert a string to a Numeric data type for precise calculations.

‍

SELECT PARSE_NUMERIC('12345.6789') as NumericValue;

‍

‍

This conversion allows for the string '12345.6789' to be used in calculations requiring decimal precision, stored as a Numeric type.

Google BigQuery SAFE_CAST

The SAFE_CAST function attempts to convert an expression to a specified data type but returns NULL instead of throwing an error if the conversion cannot be performed. This function is handy in uncertain data integrity scenarios, and avoiding query failure is crucial.

‍

Syntax:

SAFE_CAST(expression AS data_type)

‍

Here:

expression: The value or expression to convert.
data_type: The target data type for the conversion.

‍

Rules for conversion:

Conversion attempts follow the same rules as CAST but with the added safety of returning NULL on failure.

‍

Example:

Safely attempt to convert a string to an INTEGER, avoiding errors if the string is not a valid integer.

‍

SELECT SAFE_CAST('not_an_integer' AS INT64) as SafeIntegerValue;

‍

‍

This example uses SAFE_CAST to attempt converting a non-integer string to an INT64. Since the conversion is not possible, the result is NULL, preventing query failure.

Handling Errors in Conversion Using the SAFE_CAST Function

The SAFE_CAST function is a powerful tool for managing data quality issues and preventing errors during the conversion process. It is particularly beneficial in data ingestion and transformation pipelines where the data format may vary or be unpredictable.

‍

By returning NULL instead of throwing an error, SAFE_CAST ensures that queries can continue to run even when some data items are not in the expected format, thereby improving the robustness of data processing workflows in BigQuery.

Exploring Other BigQuery Conversion Functions

BigQuery's arsenal of data manipulation tools includes a variety of conversion functions beyond the widely used CAST and SAFE_CAST. These functions cater to specific data conversion needs, enabling more nuanced and precise transformations. This exploration delves into some of these additional functions, highlighting their utility and providing examples of their application in real-world scenarios.

‍

FORMAT Function

The FORMAT function in BigQuery converts various data types into a formatted string according to a specified pattern. It is handy for formatting numerical values, dates, and times in a human-readable form or a specific pattern required for reporting or data export.

‍

Syntax:

FORMAT(format_string, expression[,...])

Here:

format_string: A text string that contains format specifiers that dictate how the values of the expressions should be formatted.
expression: The data to be formatted. Multiple expressions can be included.

Example of FORMAT:

Here’s how to combine and format product ID, price, and sale date into a single string.

‍

SELECT FORMAT("Product ID: %d, Price: $%.2f, Sale Date: %tD", product_id, price, sale_date) as FormattedOutput FROM your_table;

‍

‍

This syntax takes the integer product_id, the floating-point price, and the date sale_date from the table, formatting them into a readable string. It showcases the product ID as an integer, the price in a two-decimal currency format, and the sale date in the mm/dd/yy format, providing a concise and formatted summary for reporting or analysis.

‍

In this example:

%d is a format specifier for an integer, used here to format the product_id.
%.2f is a format specifier for a floating-point number with two decimal places, used here to format the price.
%tD is a format specifier for a date, used here to format the sale_date as mm/dd/yy.

‍

PARSE_DATE and PARSE_TIMESTAMP Functions

The PARSE_DATE and PARSE_TIMESTAMP functions are invaluable for converting string representations of dates and timestamps into BigQuery's DATE and TIMESTAMP data types. These functions are also crucial for importing and analyzing text-formatted data.

‍

Syntax of PARSE_DATE:

PARSE_DATE(format_string, string_expression)

Here:

format_string: The format of the input string, using the same specifiers as the standard “strftime” function.
string_expression: The string containing the DATE to be converted.

‍

Example of PARSE_DATE:

Here’s how to convert a string to a DATE data type.

‍

SELECT PARSE_DATE('%Y-%m-%d', '2024-01-01') as NewYearDate;

‍

‍

This syntax converts the string '2024-01-01' into a DATE type, facilitating date-based computations and analyses.

‍

Syntax of PARSE_TIMESTAMP:

PARSE_TIMESTAMP(format_string, string_expression[, timezone])

Here:

format_string: The format of the input string, similar to “strftime” format specifiers.
string_expression: The string containing the TIMESTAMP to be converted.
timezone (optional): The timezone of the input string. If omitted, UTC is taken as default.

‍

Example of PARSE_TIMESTAMP:

Here’s how to convert a string to a TIMESTAMP data type, including timezone information.

‍

SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-01-01 12:00:00', 'America/New_York') as NewYearTimestamp;

‍

‍

This example demonstrates converting a string into a TIMESTAMP type, considering the 'America/New_York' timezone. This allows for precise time-sensitive data analysis.

‍

FORMAT_DATE, FORMAT_TIME, and FORMAT_TIMESTAMP Functions

To convert DATE, TIME, and TIMESTAMP data types back into formatted strings, BigQuery provides the FORMAT_DATE, FORMAT_TIME, and FORMAT_TIMESTAMP functions. These functions are essential for outputting date and time data in a specific format for reports, user interfaces, or data exports.

‍

Syntax of FORMAT_DATE:

FORMAT_DATE(format_string, date_expression)

Here:

format_string: The desired output format for the DATE.
date_expression: The DATE value to format.

‍

Example of FORMAT_DATE:

Here’s how to format a date for display in a user-friendly format.

SELECT FORMAT_DATE('%B %d, %Y', DATE '2024-01-01') as FormattedDate;

‍

‍

This query formats the date '2024-01-01' as 'January 01, 2024', making it more readable for presentation purposes.

‍

Syntax of FORMAT_TIME:

FORMAT_TIME(format_string, time_expression)

Here:

format_string: The desired output format for the time.
time_expression: The TIME value to format.

Example of FORMAT_TIME:

SELECT FORMAT_TIME('%I:%M %p', TIME '14:30:00') as FormattedTime;

‍

‍

This query formats the TIME '14:30:00' as '02:30 PM', converting from 24-hour to 12-hour format with AM/PM indication, making it more intuitive for end-users to read.

‍

Syntax of FORMAT_TIMESTAMP:

FORMAT_TIMESTAMP(format_string, timestamp_expression[, timezone])

Here:

format_string: The desired output format for the timestamp.
timestamp_expression: The TIMESTAMP value to format.
timezone (optional): The timezone for formatting the output. If omitted, UTC is assumed.

‍

Example of FORMAT_TIMESTAMP:

SELECT FORMAT_TIMESTAMP('%A, %B %d, %Y %I:%M:%S %p %Z', TIMESTAMP '2024-01-01 15:00:00 UTC', 'America/New_York') as FormattedTimestamp;

‍

‍

This query takes a TIMESTAMP value and formats it as 'Tuesday, January 01, 2024 10:00:00 AM EST', converting the UTC to Eastern Standard Time (EST) and presenting it in a format that includes the day of the week, month, day, year, time in 12-hour format, and the timezone abbreviation. This example demonstrates how to convert and format a TIMESTAMP for users in a specific locale, making the information clear and meaningful based on the specified timezone.

FORMAT Clause for CAST

The FORMAT Clause for the CAST function in BigQuery allows users to specify the output format when converting between data types. This feature is handy for converting dates, times, and numeric types to strings, enabling users to control the output format for reporting, data export, or further data processing tasks.

‍

Syntax:

CAST(expression AS STRING FORMAT format_string)

‍

Here:

expression: The value or field to be converted.
AS STRING: Indicates the target data type for the conversion.
FORMAT format_string: Specifies the format pattern the output string should follow.

‍

Rules for conversion:

The format string must be a valid format pattern applicable to the data type of the converted expression.
The format pattern follows the standard SQL date and time format strings for date and time types.
Numeric formatting follows the standard SQL numeric format strings.

Format Bytes as String

Formatting bytes as a string in BigQuery involves converting binary data (bytes) into a human-readable string format. This operation is beneficial for displaying binary information, such as encoded data or images, in a text format that can be easily read and understood.

‍

Syntax:

FORMAT('%x', bytes_expression)

‍

Here:

%x: Specifies the format to convert bytes to a hexadecimal string.
bytes_expression: The bytes value that you wish to format as a string.

‍

Example:

Convert binary data into a hexadecimal string representation.

‍

WITH DATA AS (SELECT 'Hello, World!' AS input_string ),

characters AS (

SELECT

input_string,

ARRAY_TO_STRING(ARRAY(

SELECT

FORMAT('%03d', ASCII(character))

FROM

UNNEST(SPLIT(input_string, '')) AS character), '') AS ascii_string

FROM

DATA )

SELECT

input_string,

ascii_string,

FORMAT('0x%s', ascii_string) AS hexadecimal_string

FROM

characters

‍

‍

This query first converts each character of the 'Hello, World!' string to its ASCII code, then concatenates them into one string. After this, it finally uses the FORMAT function to convert this string to hexadecimal representation.

‍

Note: BigQuery does not have a direct function to convert a string to binary data.

Format String as Bytes

Converting a string to bytes is essential for operations that require binary data, such as hashing, encryption, or when working with binary files. This conversion allows textual data to be used in contexts that require a binary format.

‍

Syntax:

CAST(string_expression AS BYTES)

‍

Here:

string_expression: The string value that you wish to convert into bytes.

‍

Example:

Here’s how to convert a string into its binary (bytes) representation.

‍

SELECT CAST('BigQuery' AS BYTES) as BytesData;

‍

‍

This query converts the string 'BigQuery' into bytes, enabling it to be used in binary data operations.

Format Date and Time as String

Formatting date and time as a string involves converting DATE or DATETIME values into a text format. This is particularly useful for generating reports, exporting data, or displaying dates and times in a more readable format.

Syntax:

FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', datetime_expression)

‍

Here:

%Y-%m-%d %H:%M:%S: The format string specifies the date and time output format.
datetime_expression: The DATE or DATETIME value to be formatted as a string.

Example:

Here’s how to format a datetime value into a string with a custom format.

‍

SELECT FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', CURRENT_DATETIME()) as FormattedDateTime;

‍

‍

This query formats the current datetime as a string in the specified format, making it readable and suitable for display or reporting purposes.

Format String as Date and Time

Converting a string to date and time involves interpreting a text representation of a date and/or time and converting it into a BigQuery DATE or DATETIME type. This conversion is useful for processing and analyzing textual date and time data.

‍

Syntax:

PARSE_DATETIME('%Y-%m-%d %H:%M:%S', string_expression)

‍

Here:

%Y-%m-%d %H:%M:%S: The format string that matches the format of the input string.
string_expression: The string containing the date and time information to be converted.

Example:

Here’s how to convert a string representing a date and time into a DATETIME type.

‍

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2024-01-01 12:00:00') as DATETIME;

‍

‍

This query converts the specified string into a DATETIME value, allowing for further date and time operations within BigQuery.

Format Numeric Type as String

Converting numeric types (such as integers or floats) to strings is crucial for concatenating with other text, displaying numbers in reports, or exporting data. This conversion allows numbers to be integrated into text-based contexts.

Syntax:

CAST(numeric_expression AS STRING)

‍

Here:

numeric_expression: The numeric value that you wish to convert into a string.

‍

Example:

Here’s how to convert a numeric value into a string for concatenation.

‍

SELECT CONCAT('The total is: ', CAST(123.45 AS STRING)) as ConcatenatedString;

‍

‍

This query converts the numeric value 123.45 into a string and concatenates it with another string, showcasing how numerical data can be integrated into text for display or reporting.

Overcoming Typical Challenges with BigQuery Conversion Functions

BigQuery's data conversion functions, like CAST, SAFE_CAST, and PARSE_NUMERIC, are powerful tools for transforming data types. However, their misuse can lead to various errors. This section explores common challenges and provides strategies for avoiding these pitfalls.

Runtime Errors with CAST

Understanding the compatibility of data types is crucial to avoid runtime errors during casting. If not followed, the following error message can pop up.

⚠️ "Error: Bad cast from type X to Y"

‍

✅ Solution: Verify that the source and target data types are compatible for conversion. When uncertain of conversion success, utilize SAFE_CAST to return NULL instead of an error.

‍

Syntax:

SAFE_CAST(expression AS data_type)

‍

Here:

expression: The data you want to convert.
data_type: The target type you want to convert the expression to.

‍

Example:

Here’s an example of converting a string to an integer, where the string might not always be valid.

SELECT SAFE_CAST('123abc' AS INT64) AS SafeInt;

‍

This query attempts to convert the string '123abc' to an integer. Since the conversion is not valid, SAFE_CAST returns NULL instead of causing a runtime error.

Overflow Errors

Be mindful of the capacity limits of the target data types to prevent overflow errors. Casting a value outside the range can show the following error message.

‍

⚠️ "Error: Numeric value out of range"

‍

✅ Solution: Ensure the value being converted fits within the capacity of the target data type. If necessary, consider using a larger data type.

‍

Syntax:

CAST(expression AS data_type)

‍

Here:

expression: The data you want to convert.
data_type: The target type you want to convert the expression to, chosen based on capacity.

‍

Example:

The following is an example of converting a large number to a smaller data type. The goal is to avoid overflow errors by selecting an appropriate target data type.

‍

Syntax:

SELECT CAST(12345678901234567890 AS FLOAT64) AS LargeNumber;

‍

This query converts a large integer to FLOAT64, avoiding overflow errors that would occur if attempting to convert to a smaller numeric type.

Invalid String Format Errors

Ensure the string format matches the expected format of the target data type, especially when converting to DATE or DATETIME. Otherwise, the following error message will pop up.

‍

⚠️ "Error: Invalid format: "string" is malformed at "X" "

‍

✅ Solution: Validate the string format before conversion, especially for dates and times, to match the target data type's expected format.

‍

Syntax:

CAST('YYYY-MM-DD' AS DATE)

‍

Here:

'YYYY-MM-DD': String representation of a DATE in the correct format.

‍

Example:

The following is an example of converting a string to a DATE. The goal is to format the string correctly for DATE conversion.

‍

Syntax:

SELECT CAST('2024-01-01' AS DATE) AS NewYearDate;

‍

This query converts a well-formatted string to a DATE, ensuring the string matches the expected DATE format to avoid format errors.

‍

Impossible Cast Between Non-castable Types

Some data types cannot be directly cast to others; understanding these limitations is essential for error-free data transformation.

‍

⚠️ "Error: Cannot cast X type to Y type"

‍

✅ Solution: Understand and respect the limitations of data type conversions. If direct casting is not possible, use intermediary conversions.

‍

Syntax:

Due to the nature of the error, the syntax is not applicable, but using functions designed for specific conversions (like PARSE_DATE for strings to dates) can be helpful.

‍

If you encounter this error, consider whether an intermediary conversion (string to integer, then integer to float) or a specialized function (like PARSE_DATE) can achieve your goal.

Errors with PARSE_NUMERIC and PARSE_BIGNUMERIC

Similar to CAST, if correct format and compatibility are not ensured, the output might show errors. The following error message will show up in such cases.

‍

⚠️ "Error: Failed to parse input string "X" "

‍

✅ Solution: Before conversion, ensure the input string is in a valid format and within the numeric range of the target data type.

‍

Syntax:

PARSE_NUMERIC('numeric_string')

‍

Here:

'numeric_string': The string representation of a numeric value you want to convert.

‍

Example:

The following is an example of parsing a numeric string to a NUMERIC type. The goal is to convert a well-formed numeric string to a NUMERIC data type.

‍

Syntax:

SELECT PARSE_NUMERIC('123.45') AS ParsedNumber;

‍

This query converts a numeric string to the NUMERIC data type, ensuring the string is correctly formatted and within the NUMERIC type's range to avoid parse errors.

‍

Error While Handling Inconsistent Date Formats with PARSE_DATE

When working with inconsistent date formats, analysts tend to use PARSE_DATE to convert the strings into dates. If the correct format and compatibility are not ensured, the output might show errors. The following error message will show up in such cases.

‍

⚠️ "Error: Failed to parse input string "X" "

‍

✅ Solution:

Before conversion, ensure the input string is in a valid format and compatible with the target data type. If the format is not guaranteed, use SAFE.PARSE_DATE to handle potential errors gracefully by returning NULL instead of causing an error.

‍

Syntax:

SELECT

SAFE.PARSE_DATE('format1', date_string) AS parsed_date_format1,

SAFE.PARSE_DATE('format2', date_string) AS parsed_date_format2,

SAFE.PARSE_DATE('format3', date_string) AS parsed_date_format3

FROM

your_table;

‍

Example:

You have a table events with a column event_date that contains date strings in various formats like %Y-%m-%d, %d-%m-%Y, and %m/%d/%Y. Identify

‍

Multiple Date Formats:

format1 = %Y-%m-%d (e.g., 2023-07-25)

format2 = %d-%m-%Y (e.g., 25-07-2023)

format3 = %m/%d/%Y (e.g., 07/25/2023)

‍

Attempt to parse the date string with each format using SAFE.PARSE_DATE

to avoid errors with the following syntax.

SELECT

event_id,

SAFE.PARSE_DATE('%Y-%m-%d', event_date) AS parsed_date_format1,

SAFE.PARSE_DATE('%d-%m-%Y', event_date) AS parsed_date_format2,

SAFE.PARSE_DATE('%m/%d/%Y', event_date) AS parsed_date_format3

FROM

events;

‍

The query will run without errors, and any invalid date strings will be replaced with NULL. This allows you to handle or clean up the incorrect data separately.

‍

Here:

event_id: Unique identifier for each event.
event_date: Column containing date strings in various formats.
SAFE.PARSE_DATE('%Y-%m-%d', event_date) AS parsed_date_format1: Attempts to parse event_date using the %Y-%m-%d format. If it fails, NULL is returned.
SAFE.PARSE_DATE('%d-%m-%Y', event_date) AS parsed_date_format2: Attempts to parse event_date using the %d-%m-%Y format. If it fails, NULL is returned.
SAFE.PARSE_DATE('%m/%d/%Y', event_date) AS parsed_date_format3: Attempts to parse event_date using the %m/%d/%Y format. If it fails, NULL is returned.

‍

Combining Results:

You might want to combine the results to get a single parsed date from the multiple attempts:

‍

SELECT event_id, COALESCE( SAFE.PARSE_DATE('%Y-%m-%d', event_date), SAFE.PARSE_DATE('%d-%m-%Y', event_date), SAFE.PARSE_DATE('%m/%d/%Y', event_date) ) AS parsed_date FROM events;

‍

COALESCE returns the first non-NULL value among its arguments.

This query attempts to parse event_date using three different formats and returns the first successfully parsed date.

‍

By following this approach, you can handle multiple date formats gracefully without causing your query to fail due to invalid date strings.

Elevate Your Data Insights with OWOX BI BigQuery Reports Extension

Elevate your data insights with the OWOX BI BigQuery Reports Extension, a powerful tool to integrate advanced data conversion functions into your analytics workflows. This extension simplifies complex data transformation tasks, enabling sophisticated data manipulation and refined data preparation for in-depth analysis.

‍

By leveraging BigQuery's CAST and SAFE_CAST functions through OWOX BI BigQuery Extension, data professionals can effortlessly convert data types, format DATES, and strings, and ensure their analyses are based on accurate and reliable data.

With improved data manipulation capabilities and error-free information, you can uncover hidden trends, make informed decisions, and drive your business forward with confidence

‍