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, Looker 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).