Conversion Functions
This page contains the explicit conversion functions supported by e6data.
CAST FUNCTION
CAST( <expr> as <target datatype> )
Converts the input expression to the specified target datatype.
The following table contains a matrix of all supported conversions:
Source(Row) Target(Column) | varchar | integer/int | bigint | float | double | boolean | date | timestamp |
---|---|---|---|---|---|---|---|---|
varchar | Y | Y | Y | Y | Y | Y | Y | Y |
integer/int | Y | Y | Y | Y | Y | Y | - | - |
bigint | Y | Y | Y | - | - | - | - | - |
float | Y | Y | - | Y | Y | - | - | - |
double | Y | Y | Y | Y | Y | - | - | - |
boolean | Y | - | - | - | - | Y | - | - |
date | Y | - | - | - | - | - | Y | Y |
timestamp | Y | - | - | - | - | - | Y | Y |
TRY CAST FUNCTION
TRY_CAST( <input datatype> , <output datatype> )
Converts the input expression to the specified target datatype, but returns a NULL value instead of raising an error when the conversion can not be performed.
Usages
Support datatype: VARCHAR, NUMBER (or any of its synonyms), DOUBLE, BOOLEAN, DATE, TIMESTAMP
try_cast to DATE accept 'YYYY-MM-DD' format for other formats returns null
try_cast to TIMESTAMP accept 'YYYY-MM-DD HH:mm:ss' format for other formats returns null
FORMAT( <format_string>, <value>)
Converts the input expression to the specified target datatype.
This function produces a string in a specific format by utilising the provided format string and arguments.
Support datatype: INT/FLOAT/DOUBLE/STRING/DATE/TIMESTAMP/BOOL
Usages: We support java format string
TO_BOOLEAN( <expr> )
Converts and returns the boolean value of the input. For null input, output is also NULL Support datatype: INT/DOUBLE/VARCHAR/BOOLEAN
String Conversion
Strings converted to TRUE:
'true'
,'t'
,'yes'
,'on'
,'1'
.Strings converted to FALSE:
'false'
,'f'
,'no'
,'off'
,'0'
.All other text strings cannot be converted to Boolean values.
Numeric Conversion
Zero (
0
) is converted to FALSE.One (
1
) is converted to TRUE.Any non-zero value other than
1
is converted to FALSE.
TO_DATE ( <value>, <format_string>)
This function converts an input expression into a date.
Support datatype: STRING/DATE/TIMESTAMP
Usages: we support java format string for format string. However we are not supporting timezone ID and timezone name in the format.
TO_TIMESTAMP( <value>, <format_string>)
This function converts an input expression into a date.
Support datatype: STRING/DATE/TIMESTAMP
Usages: we support java format string for format string. However we are not supporting timezone ID and timezone name in the format.
TO_TIMESTAMP_TZ( <value>, <format_string>)
Returns Timestamp with Time Zone Information
Support datatype: DATE/TIMESTAMP/INT/DOUBLE/VARCHAR(Only yyyy-MM-dd HH:mm:ss)
Usages: we support java format string for format string. However we are not supporting timezone ID and timezone name in the format.
Last updated