Type conversion functions help change the data type of variables from one to another. Common examples are converting strings to dates or numbers, or converting dates into strings. See the sections on Date/time formatting strings and Numeric formatting strings for more information on using the format or formatstr parameters mentioned below. This subsection may produce errors if the variables you provide are not of the correct data type, or the format string is not appropriate for the data. See the subsection below this for alternative type conversion functions that handle specification errors more cleanly.
| Function | Usage and description |
| Convert to Character |
Usage: TO_CHAR(expression, formatstr) Converts the data (e.g., another variable) provided in expression to a character based on the setting formatstr. In addition, a very simple way to convert numeric data to string data is to use the concatenation operator with an empty string. For example, the code 123.0 || '' (note that is two single quotes with no space between) will return the string "123.0". |
| Convert String To Date |
Usage: TO_DATE(string,format) This function takes a string and a date format string as inputs. The date format string should describe how the string is formatted as a date. It returns the string in DATE or DATETIME format, based on the provided format string. See the Date/Time Format Strings section for an overview of how to construct the format string to match the structure of the provided string. |
| Convert String To Number-simple |
The simplest method to convert a string variable that has digits in the string is to multiply it by 1.0. For example, 1.0*'123' results in 123.0. The string must not have any characters that cannot be interpreted in a numeric framework or an error will result. |
| Convert String To Number |
Usage: TO_NUMBER(string,format) This function takes an string and a numeric format string as inputs. The numeric format string should describe how the string is formatted as a number. It returns the numeric value of the string, based on the provided format string. See the Numeric Format Strings section for an overview of how to construct the format string to match the structure of the provided string. |
The additional functions below are better at handling errors in the input data. For example, if a string variable is converted to a number, but some values of the string do not convert well (such as 'ABC'), these functions will produce NULL results for those situations instead of erroring out. This list also includes other type checking functions that can be used to determine (perhaps within a CASE/WHEN statement) what type of data your are working with dynamically.
| Function | Usage and description |
| str_to_date |
Usage: str_to_date(str, formatstr) Converts the string str to a date variable based on the provided formatstr. Valid values to use in the formatstr are documented at https://support.lityxiq.com/074111-DateTime-Formatting-Strings-for-str_to_date. If the string is not formatted correctly, a NULL value is returned. |
| str_to_datetime |
Usage: str_to_datetime(str, formatstr) Converts the string str to a datetime variable based on the provided formatstr. Valid values to use in the formatstr are documented at https://support.lityxiq.com/074111-DateTime-Formatting-Strings-for-str_to_date. If the string is not formatted correctly, a NULL value is returned. |
| str_to_int |
Usage: str_to_int(str) Converts the string str to an integer. If the string is not formatted in a way that is easily converted to integer (such as the string 'ABC'), a NULL value is returned. If the string is formatted as a decimal value, such as '328.3', it will convert to an integer and be rounded off. |
| str_to_decimal |
Usage: str_to_decimal(str) Converts the string str to an decimal valued variable. If the string is not formatted in a way that is easily converted to a number (such as the string 'ABC'), a NULL value is returned. Strings formatted like integers, such as '294' will be converted to the equivalent decimal version. |
| number_to_decimal |
Usage: number_to_decimal(nbr) Converts the numeric variable nbr to an decimal valued variable. Nbr can be either an integer or decimal variable. A NULL value will be returned in the case of a NULL or incorrect input. |
| number_to_int |
Usage: number_to_int(nbr) Converts the numeric variable nbr to an integer valued variable. Nbr can be either an integer or decimal variable; if it is a decimal, it will be rounded off to create the resulting integer. A NULL value will be returned in the case of a NULL or incorrect input. |
| string_isvaliddate |
Usage: string_isvaliddate(str, formatstr) Evaluates the input str and returns a true or false depending on whether it is a valid date based on the provided formatstr. Valid values to use in the formatstr are documented elsewhere. This function can be used within CASE/WHEN statements to help determine how to treat or convert a variable. |
| string_isvaliddecimal |
Usage: string_isvaliddecimal(str) Evaluates the input str and returns a true or false depending on whether it represents a valid decimal value. This function can be used within CASE/WHEN statements to help determine how to treat or convert a variable. |
| string_isvalidint |
Usage: string_isvalidint(str) Evaluates the input str and returns a true or false depending on whether it represents a valid integer value. This function can be used within CASE/WHEN statements to help determine how to treat or convert a variable. |