Looker Studio is now called Data Studio. Learn more about this change.

CAST

Converts a field or expression from one type to another type.

Sample usage

CAST(Number_field AS TEXT)
CAST(Text_field AS NUMBER)
CAST(Date_field as DATETIME)

Syntax

CAST field_expression AS type

Parameters

field_expression - a field or expression.
type - a data type literal.

`CAST` data type literals

You can cast from or to the following types:

From type	To type
Text	`DATE` `DATETIME` `NUMBER`
Number	`TEXT`
Date	`DATETIME` `TEXT`
Date & Time	`DATETIME` `TEXT`

Examples

Goal: Count website sessions longer than N seconds.

In a Google Analytics data source, Session Duration is a text dimension. The first step to counting sessions longer than N is to convert this to a number using CAST. The second step is to use the CASE statement to identify sessions longer than a certain value—we'll say 60 seconds for this example. We'll combine CASE with SUM to do the counting.

Step 1: Create a new calculated field Session Duration (number) with the following formula:

CAST(Session Duration AS NUMBER)

Step 2: Create a new calculated field Long Sessions :

SUM(CASE WHEN Session Duration > 60 THEN 1 ELSE 0 END)

Notes

You cannot CAST aggregated fields.

Casts from [DATE|DATETIME] to TEXT return the canonical string formats shown following. To return a different format, use the FORMAT_DATETIME function.
Casts from TEXT to [DATE|DATETIME] accept only the canonical string format. To parse a different format, use the PARSE_* functions.

Type	Canonical STRING format
DATE	`YYYY-\[M\]M-\[D\]D`
DATETIME	`YYYY-\[M\]M-\[D\]D \[\[H\]H:\[M\]M:\[S\]S\[.DDDDDD\]\]`

Coercion to text

When an expression requires a text string, Data Studio coerces (implicitly converts) non-string values to a string data type.

Type	Canonical STRING format
DATE	`YYYY-MM-DD`
DATETIME	`YYYY-MM-DD HH:MM:SS\[.DDD\[DDD\]\]`

For example, suppose we have a created_datetime field of type DATETIME and the following formula:

CONCAT("Created on ", created_datetime)

This formula returns the following:

Created on 2019年11月22日 16:30:00.739182

This formula doesn't require an explicit CAST(created_datetime AS TEXT).