CAST Operator

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

* VARCHAR to DATE / TIMESTAMP types only support strings matching the format of YYYY-MM-DD HH:MI:SS.MS/US. If you have a varchar of other format, please use the TO_DATE / TO_TIMESTAMP function instead. Similarly, for INTERVAL we only support strings of format X or X-Y where X and Y are string integers respectively.

* When casting a VARCHAR that includes a timezone to a TIMESTAMP (without timezone), Vertica will consider the timezone and return in UTC, but Databricks will ignore the timezone.

Casting to VARCHAR Differences

Interval Types

All Interval types to VARCHAR are different for Vertica and Databricks. Databricks will include the interval type (i.e. INTERVAL ... YEAR TO MONTH in the output string, but Vertica will only return the value.

Example:

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

-- Vertica returns: '100-0'
-- Databricks returns: 'INTERVAL '100-0' YEAR TO MONTH'

Timezones

TIMESTAMP WITH TIME ZONE to VARCHAR will always return in the UTC timezone. Vertica will show +00 in the string, but Databricks will not.