Conversion Functions in Analytics

The conversion functions convert a value from one form to another. You can also use
the VALUEOF function in a filter to reference the value of an Analytics system variable.

Functions

CAST

This function 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). The following are the supported data
types to which the value can be changed:

CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE,
TIME, TIMESTAMP, BIT, BIT VARYING

Depending on the source data type, some destination types are not supported.
For example, if the source data type is a BIT string, the destination data type must
be a character string or another BIT string.

Use CAST to change to a DATE data type. Do not use TO_DATE.

The following describes unique characteristics of the CHAR and VARCHAR data
types:

• Casting to a CHAR data type. You must use a size parameter. If you do not
add a size parameter, a default of 30 is added. Syntax options appear in the
following list:

The recommended syntax is:
CAST(expr|NULL AS CHAR(n))

For example:
CAST(companyname AS CHAR(35))

You can also use the following syntax:
CAST(expr|NULL AS data_type)

For example:
CAST(companyname AS CHAR)

note: If you use this syntax, the Analytics Server explicitly converts and stores as
CAST(expr|NULL AS CHAR(30))

• Casting to a VARCHAR data type. You must use a size parameter. If you omit
the size parameter, you cannot can save the change.

 

 

IFNULL

This function tests if an expression evaluates to a null value, and if it does, assigns
the specified value to the expression.

Syntax

IFNULL(expr, value)

Where

expr is the expression to evaluate.
value is the value to assign if the expression evaluates to a null value.

 

 

TO_DATETIME

This function converts string literals of dateTime format to a DateTime data type.

Syntax

TO_DATETIME(‘string1’, ‘DateTime_formatting_string’)

Where

string1 is the string literal you want to convert
DateTime_formatting_string is the DateTime format you want to use, such as
yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month,
dd represents day, hh represents hour, mi represents minutes, and ss represents seconds.