CAST Function

Casts the value expr to the target data type.

Syntax

cast(expression AS dataType)

Parameters

expression: An expression of any type
dataType: A SQL data type that CeloSQL supports

Returns

The result of expression in type specified by dataType.

The following table defines allowed type casts:

Source Type	Target Types
BOOLEAN	INTEGER, BIGINT, VARCHAR
INTEGER	BOOLEAN, BIGINT, DOUBLE, DECIMAL, INTERVAL YEAR, INTERVAL MONTH, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
BIGINT	BOOLEAN, INTEGER, DOUBLE, DECIMAL, INTERVAL YEAR, INTERVAL MONTH, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
DOUBLE	INTEGER, BIGINT, DECIMAL, VARCHAR
DECIMAL	INTEGER, BIGINT, DOUBLE, VARCHAR
DATE	TIMESTAMP, TIMESTAMP WITH TIME ZONE, VARCHAR
TIMESTAMP	DATE, TIMESTAMP WITH TIME ZONE, VARCHAR
TIMESTAMP WITH TIME ZONE	DATE, TIMESTAMP, VARCHAR
INTERVAL YEAR TO MONTH	INTERVAL YEAR, INTERVAL MONTH, VARCHAR
INTERVAL DAY TO HOUR	INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL DAY TO MINUTE	INTERVAL DAY TO HOUR, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL DAY TO SECOND	INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL HOUR TO MINUTE	INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL HOUR TO SECOND	INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL MINUTE TO SECOND	INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL YEAR	INTEGER, BIGINT, INTERVAL YEAR TO MONTH, INTERVAL MONTH, VARCHAR
INTERVAL MONTH	INTEGER, BIGINT, INTERVAL YEAR TO MONTH, INTERVAL YEAR, VARCHAR
INTERVAL DAY	INTEGER, BIGINT, INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL HOUR, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL HOUR	INTEGER, BIGINT, INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL MINUTE, INTERVAL SECOND, VARCHAR
INTERVAL MINUTE	INTEGER, BIGINT, INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL SECOND, VARCHAR
INTERVAL SECOND	INTEGER, BIGINT, INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE TO SECOND, INTERVAL DAY, INTERVAL HOUR, INTERVAL MINUTE, VARCHAR
VARCHAR	BOOLEAN, INTEGER, BIGINT, DOUBLE, DECIMAL, DATE^, TIMESTAMP^, TIMESTAMP WITH TIME ZONE^, INTERVAL YEAR TO MONTH^, INTERVAL DAY TO HOUR^, INTERVAL DAY TO MINUTE^, INTERVAL DAY TO SECOND^, INTERVAL HOUR TO MINUTE^, INTERVAL HOUR TO SECOND^, INTERVAL MINUTE TO SECOND^, INTERVAL YEAR^, INTERVAL MONTH^, INTERVAL DAY^, INTERVAL HOUR^, INTERVAL MINUTE^, INTERVAL SECOND^, VARBINARY
VARBINARY

* Casting from VARCHAR to DATE or TIMESTAMP types only supports strings matching the format YYYY-MM-DD HH:MI:SS.MS/US. For other formats, use the TO_DATE or TO_TIMESTAMP functions. Similarly, for INTERVAL types, only strings in the format X or X-Y (where X and Y are string integers) are supported.

* When casting a VARCHAR that includes a timezone to a TIMESTAMP (without timezone), Vertica consider the timezone and return the value in UTC, whereas Spark-based engine typically ignore the timezone.

Casting to VARCHAR differences

Interval types

The conversion of interval types to VARCHAR differs between execution layers. Spark-based engine include the full interval definition (e.g., INTERVAL ... YEAR TO MONTH) in the output string, whereas Vertica return only the value.

Example:

SELECT CAST(INTERVAL '100-0' YEAR TO MONTH AS VARCHAR);

-- Result in Vertica: '100-0'
-- Result in Spark-based engine: "INTERVAL '100-0' YEAR TO MONTH"

Timezones

Casting a TIMESTAMP WITH TIME ZONE to VARCHAR always returns the value in the UTC timezone. However, the string formatting varies: Vertica include the UTC offset (+00) in the string, while Spark-based engine does not.