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.

cast('2022-01-11' as date)

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.

> select try_cast('45.6789' AS double)
45.6789

> select try_cast('false' as boolean)
false

> select try_cast('2022-01-02 12:27:11' as TIMESTAMP)
2022-01-02T12:27:11.000+00:00

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

  > select try_cast('05-Mar-2014' as date) 
  NULL

• try_cast to TIMESTAMP accept 'YYYY-MM-DD HH:mm:ss' format for other formats returns null

  > select try_cast('05-Mar-2014 12:27:11' as timestamp) 
  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

> select format('%s%%', 9887);
9887%

> select format('%.2f', pi());
3.14

> select format('%,.2f', 167.989);
167.99

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

> select to_boolean('1')
true

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

> select to_date('2024-12-16 12:30:45','yyyy-MM-dd');
2024-12-16

> select to_date('2024.07.23', 'yyyy.MM.dd');
2024-07-23

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

> select to_timestamp('04/13/2024 15:49:03', 'MM/dd/yyyy HH:mm:ss');
2024-04-13T15:49:03.000+00:00

> select to_timestamp('2024.07.23', 'yyyy.MM.dd');
2024-07-23T00:00:00.000+00:00

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)

> select 
    to_timestamp_tz('2023-02-14 06:30:00'),
    to_timestamp_tz('2023-02-14 06:30:00.345','yyyy-MM-dd HH:mm:ss.S')

|2023-02-14T06:30:00.000+00:00    |2023-02-14T06:30:00.345+00:00

Usages: we support java format string for format string. However we are not supporting timezone ID and timezone name in the format.