# Conversion Functions ## CAST FUNCTION #### CAST( \ as \ ) Converts the input expression to the specified target datatype. ```sql 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( \ , \ ) 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

```sql > select try_cast('false' as boolean) false ``` ```sql > 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 ```sql > 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 ```sql > select try_cast('05-Mar-2014 12:27:11' as timestamp) NULL ``` #### FORMAT( \, \) 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 ```sql > select format('%s%%', 9887); 9887% > select format('%.2f', pi()); 3.14 > select format('%,.2f', 167.989); 167.99 ``` **Usages**: We support java [format string](https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/util/Formatter.html#syntax) **TO\_BOOLEAN(**** \ ****)** 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 (**** \, \****)** 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](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#ISO_DATE) for format string. However we are not supporting timezone ID and timezone name in the format. #### TO\_TIMESTAMP( \, \) 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](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#ISO_DATE) for format string. However we are not supporting timezone ID and timezone name in the format. #### TO\_TIMESTAMP\_TZ( \, \) 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](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#ISO_DATE) for format string. However we are not supporting timezone ID and timezone name in the format. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.e6data.com/product-documentation/sql-command-reference/conversion-functions.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.