# 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)varcharinteger/intbigintfloatdoublebooleandatetimestamp
varcharYYYYYYYY
integer/intYYYYYY--
bigintYYY-----
floatYY-YY---
doubleYYYYY---
booleanY----Y--
dateY-----YY
timestampY-----YY
## 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.