SQL Function CAST
Syntax
CAST(value AS datatype[precision,scale])
Description
The CAST() function in Datameer's SQL worksheet converts a value into a specified data type.
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 Datameer | |
|---|---|
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 |
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 |