Conversion Functions

Conversion functions convert a value from one form to another.

Function	Example	Description	Syntax
CAST	CAST(hiredate AS CHAR(40)) FROM employee	Changes the data type of an expression or a null literal to another data type. For example, you can cast a customer_name (a data type of CHAR or VARCHAR) or birthdate (a datetime literal). Use CAST to change to a Date data type. Don’t use TODATE.	CAST(expr AS type)
IFNULL	IFNULL(Sales, 0)	Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.	IFNULL(expr, value)
INDEXCOL	SELECT INDEXCOL(VALUEOF ("NQ_SESSION"."GEOGRAPHY_LEVEL"), Country, State, City), Revenue FROM Sales	Uses external information to return the appropriate column for the signed-in user to see.	INDEXCOL([integer literal], [expr1] [, [expr2], ?-])
NULLIF	SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name, "Old Job ID";	Compares two expressions. If they’re equal, then the function returns NULL. If they’re not equal, then the function returns the first expression. You can’t specify the literal NULL for the first expression.	NULLIF([expression], [expression])
To_DateTime	SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales	Converts string literals of DateTime format to a DateTime data type.	To_DateTime([expression], [literal])
VALUEOF	SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")	References the value of a semantic model variable in a filter. Use expr variables as arguments of the VALUEOF function. Refer to static semantic model variables by name.	VALUEOF(expr)