SQL Function CAST

Syntax

CAST(value AS datatype [precision,scale])

Description

The CAST() function in Spectrum's SQL worksheet converts a value into a specified data type.

Example

SQL command	Returns	Column type
SELECT CAST(100.05 AS INTEGER)	100	String
SELECT CAST(100.05 AS FLOAT)	100.05	Float
SELECT CAST(100.05 AS DECIMAL(10,0))	100	Integer
SELECT CAST(100.05 AS DECIMAL(25,0))	100.05	BigInteger
SELECT CAST(100.05 AS DECIMAL(15,2))	100.05	Float
SELECT CAST(100.05 AS DECIMAL(18,2))	100.05	BigDecimal
SELECT CAST(100.05 AS DECIMAL)	100	BigInteger
SELECT CAST(100.05 AS VARCHAR)	100.05	String
SELECT CAST(true AS BOOLEAN)	True	Boolean
SELECT CAST('2019-02-14' AS TIMESTAMP)	Feb 14, 2019 12:00:00 AM	Date

Possible values type casts

Source data type	to CAST as data type
any Number	INTEGER, DECIMAL, FLOAT
String of Number	INTEGER, DECIMAL, FLOAT
String of true/false	BOOLEAN
String of Timestamp	TIMESTAMP
any Type	VARCHAR

Cast data type conversions in Spectrum

AS TYPE[(precision,scale)]	resulting SheetColumnType
INTEGER	Integer
FLOAT	Float
DECIMAL(<20,0)	Integer
DECIMAL(>=20,0)	BigInteger
DECIMAL(<16,>=1)	Float
DECIMAL(>=16,>=1)	BigDecimal
DECIMAL	BigInteger
VARCHAR	String
BOOLEAN	Boolean
TIMESTAMP	Date