Conversion Functions

Function

Description

Cast(Arg1 as type)

Casts any expression to any type (except Null/Undefined).

Invalid values are propagated. Casting performed for different types of input and output types results in different outputs. See Cast Method for more information.

Example:

Cast([IntegerColumn] as Currency)

Boolean(Arg1)

Converts the column or value to a Boolean.

Example:

Boolean([Column])

Currency(Arg1)

Converts the column or value to a Currency.

Example:

Currency([Column])

Date(Arg1, ..., Arg3)

Converts the column or values to a Date. If a single argument is used, Arg1 can be of type String or DateTime. If a String is specified, the date must be written in a format that Spotfire can recognize. Additionally, all parts of the date (year, month and day) must be present. See examples below. If a DateTime is specified, the time part is removed.

If three integer arguments are given, then the first argument is the year, the second is the month and the third is the day of the month.

See also Date and Time functions.

Examples:
Date("2003-03-21")                        -> 3/21/2003

Date("3/21/03")                               -> 3/21/2003
Date("10")                                        -> (Empty)
Date(null)                                         -> (Empty)
Date("2003-03-21 11:37:00")       -> 3/21/2003

Date(2003,03, 21)                          -> 3/21/2003

(The output formats available are dependent on your current locale.)

DateTime(Arg1, Arg2,..., Arg7)

Converts the column or values to a DateTime. If a single argument is used, Arg1 can be of type String or Date. If a String is specified, the date must be written in a format that Spotfire can recognize. Additionally, at least all parts of the date (year, month and day) must be present. If a Date is specified, the time part is set to 00:00:00 (12:00:00 AM).

If seven integer arguments are given, then the first argument is the year, the second is the month, the third is the day of the month, the fourth is the hour, the fifth is the minute, the sixth is the second and the seventh argument is the millisecond.

See also Date and Time functions.

Examples:
DateTime("2003-03-21 11:37:00")    -> 3/21/2003 11:37:00 AM
DateTime("10")                                      -> (Empty)  
DateTime(null)                                       -> (Empty)
DateTime("2003-03-21")                      -> 2003-03-21 00:00:00

DateTime(2003, 03, 21, 11, 37, 00)   -> 2003-03-21 11:37:00

(The output formats available depend on your current locale.)

Integer(Arg1)

Converts the column or value to an integer number. If the conversion fails, an error is returned. Arg1 can be of type integer, real or string, and the result is of type integer. Real numbers are truncated, i.e., only the integer part is used.

Examples:
Integer("-123")       -> -123
Integer(-2.99)        -> -2
Integer("0%")         -> (Empty)
Integer(1e20)        -> (Empty)

Integer(null)           -> (Empty)

LongInteger(Arg1)

Converts the column or value to a LongInteger.

Example:

LongInteger([Column])

Real(Arg1)

Converts the column or value to a real number. If the conversion fails, an error is returned. Arg1 can be of type integer, real or string, and the result is of type real.

Examples:
Real(1.23)      -> 1.23
Real(2)            -> 2
Real("0%")      -> (Empty)
Real(null)        -> (Empty)

SingleReal(Arg1)

Converts the column or value to a SingleReal.

Example:

SingleReal([Column])

SN(Arg1, Arg2)

Substitutes null values. Returns Arg1 if it is not null, Arg2 otherwise. Arg1 and Arg2 can be of any type, but both must be of the same type or null. The result is of the same type as the arguments.

A common usage is to substitute null values in a column. If Arg1 is a column, Arg2 can be either a value of the same type as the contents of the column or a column with the same content type. If Arg2 is also a column, the error in Arg1 will be replaced with the value from the same row in Arg2.

Examples:
SN(1, 2)        -> 1
SN(null, 2)    -> 2
SN(0/0, 2)     -> #NA

SN([Column], 1) -> 1 (if null value in column)

SN([Column1], [Column2]) -> (value from Column2 if null value in Column1)

String(Arg1)

Converts the column or value to a string. This conversion never fails except if Arg1 is null. Arg1 can be of any type and the result is of type string.

Examples:
String(1.23)     -> "1.23"
String(null)      -> (Empty)

Time(Arg1, Arg2,..., Arg4)

Converts the column or values to a time. If the conversion fails, an error is returned. If a single argument is used, Arg1 can be of type String or DateTime.  If a String is specified, the time must be written in a format that Spotfire can recognize. Additionally, both the hour and the minute must be specified. See examples below. If a DateTime is specified, the date part is removed.

If four integer arguments are given, then the first argument is the hour, the second is the minute, the third is the second and the fourth is the millisecond.

See also Date and Time functions.

Examples:
Time("11:37:00")                         -> 11:37:00
Time("10")                                    -> (Empty)
Time (null)                                    -> (Empty)
Time(11, 30, 20, 4)                       ->11:30:20

(The output formats available depend on your current locale.)

TimeSpan(Arg1, Arg2,...Arg5))

Creates a TimeSpan from a column or values. If a single argument is given, the input column can be of type String or TimeSpan. If a String is specified, TimeSpan must be written in the format "[-]d.h:m:s.ms".

If five arguments are given, then the first argument is the days, the second is the hours, the third is the minutes, the fourth is the seconds and the fifth is the milliseconds. The first four arguments are integers, the last is a real number.

Examples:

TimeSpan([Column])

TimeSpan("247.5:17:11.5002")

TimeSpan(247, 05, 17, 11, 500.2)