Date and time functions

The following table lists the functions that are available in the Date and Time category (Square brackets
indicate an argument is optional):

Name Description Arguments Output

DateFromDaysSince Returns a date by number (int32) date
adding an integer to a [baseline date]
baseline date
DateFromJulianDay Returns a date from juliandate (uint32) date
the given julian date
DaysSinceFromDate Returns the number source_date days since (int32)
of days from source given_date
date to the given date
HoursFromTime Returns the hour time hours (int8)
portion of a time
JulianDayFromDate Returns julian day date julian date (int32)
from the given date
MicroSecondsFromTime Returns the time microseconds
microsecond portion (int32)
from a time
MinutesFromTime Returns the minute time minutes (int8)
portion from a time
MonthDayFromDate Returns the day of the date day (int8)
month given the date
MonthFromDate Returns the month date month number
number given the date (int8)
NextWeekdayFromDate Returns the date of source date date
the specified day of day of week (string)
the week soonest after
the source date
PreviousWeekdayFromDate Returns the date of source date date
the specified day of day of week (string)
the week most recent
before the source date
SecondsFromTime Returns the second time seconds (dfloat)
portion from a time
 Name Description Arguments Output
SecondsSinceFromTimestamp Returns the number timestamp base seconds (dfloat)
of seconds between timestamp
two timestamps
TimeDate Returns the system - system time and
time and date as a date (string)
formatted string
TimeFromMidnightSeconds Returns the time seconds (dfloat) time
given the number of
seconds since
midnight
TimestampFromDateTime Returns a timestamp date time timestamp
form the given date
and time
TimestampFromSecondsSince Returns the seconds (dfloat) timestamp
timestamp from the [base timestamp]
number of seconds
from the base
timestamp
TimestampFromTimet Returns a timestamp timet (int32) timestamp
from the given unix
time_t value
TimetFromTimestamp Returns a unix time_t timestamp timet (int32)
value from the given
timestamp
WeekdayFromDate Returns the day date [origin day] day (int8)
number of the week
from the given date.
Origin day optionally
specifies the day
regarded as the first in
the week and is
Sunday by default
YeardayFromDate Returns the day date day (int16)
number in the year
from the given date
YearFromDate Returns the year from date year (int16)
the given date
 Name Description Arguments Output
YearweekFromDate Returns the week date week (int16)
number in the year
from the given date

Date, Time, and Timestamp functions that specify dates, times, or timestamps in the argument
use strings with specific formats:

For a date, the format is %yyyy-%mm-%dd

For a time, the format is %hh:%nn:%ss, or, if extended to include microseconds, %hh:%nn:
%ss.x where x gives the number of decimal places seconds is given to.

For a timestamp the format is %yyyy-%mm-%dd %hh:%nn:%ss, or, if extended to include

microseconds, %yyyy-%mm-%dd %hh:%nn:%ss.x where x gives the number of decimal places
seconds is given to.

This applies to the arguments date, baseline date, given date, time, timestamp, and base
timestamp.

Functions that have days of week in the argument take a string specifying the day of the week,
this applies to day of week and origin day.

 Date and time formats

Parallel jobs provide flexible handling of date and time formats.

Parent topic: Functions used in IBM WebSphere DataStage and QualityStage

Logical Functions
The following table lists the functions available in the Logical category (square brackets indicate an
argument is optional):

Name Description Arguments Output

Not Returns the complement of the logical value of expression Complement
an expression (int8)
BitAnd Returns the bitwise AND of the two integer number 1 (uint64) number
arguments number 2 (uint64) (uint64)
 Name Description Arguments Output
BitOr Returns the bitwise OR of the two integer number 1 (uint64) number
arguments number 2 (uint64) (uint64)
BitXOr Returns the bitwise Exclusive OR of the two number 1 (uint64) number
integer arguments number 2 (uint64) (uint64)
BitExpand Returns a string containing the binary number (uint64) string
representation in "1"s and "0"s of the given
integer
BitCompress Returns the integer made from the string number (string) number
argument, which contains a binary (uint64)
representation of "1"s and "0"s.
SetBit Returns an integer with specific bits set to a origfield (uint64) number
specific state, where bitlist (string) (uint64)
bitstate (uint8)
origfield is the input value to perform the
action on,

bitlist is a string containing a list of comma

separated bit numbers to set the state of, and
bitstate is either 1 or 0, indicating which state
to set those bits.
Parent topic: Functions used in IBM WebSphere DataStage and QualityStage

Mathematical Functions
The following table lists the functions available in the Mathematical category (square brackets indicate
an argument is optional):

Name Description Arguments Output

Abs Absolute value of any numeric expression number (int32) result
(dfloat)
Acos Calculates the trigonometric arc-cosine of an number (dfloat) result
expression (dfloat)
Asin Calculates the trigonometric arc-sine of an number (dfloat) result
expression (dfloat)
 Name Description Arguments Output
Atan Calculates the trigonometric arc-tangent of an number (dfloat) result
expression (dfloat)
Ceil Calculates the smallest dfloat value greater than or number (decimal) result
equal to the given decimal value (dfloat)
Cos Calculates the trigonometric cosine of an number (dfloat) result
expression (dfloat)
Cosh Calculates the hyperbolic cosine of an expression number (dfloat) result
(dfloat)
Div Outputs the whole part of the real division of two dividend (dfloat) result
real numbers (dividend, divisor) divisor (dfloat) (dfloat)
Exp Calculates the result of base 'e' raised to the power number (dfloat) result
designated by the value of the expression (dfloat)
Fabs Calculates the absolute value of the given value number (dfloat) result
(dfloat)
Floor Calculates the largest dfloat value less than or number (decimal) result
equal to the given decimal value (dfloat)
Ldexp Calculates a number from an exponent and mantissa (dfloat) result
mantissa exponent (int32) (dfloat)
Llabs Returns the absolute value of the given integer number (uint64) result
(int64)
Ln Calculates the natural logarithm of an expression number (dfloat) result
in base 'e' (dfloat)
Log10 Returns the log to the base 10 of the given value number (dfloat) result
(dfloat)
Max Returns the greater of the two argument values number 1 (int32) result
number 2(int32) (int32)
Min Returns the lower of the two argument values number 1 (int32) result
number 2 (int32) (int32)
Mod Calculates the modulo (the remainder) of two dividend (int32) result
expressions (dividend, divisor) divisor (int32) (int32)
Neg Negate a number number (dfloat) result
(dfloat)
Pwr Calculates the value of an expression when raised expression (dfloat) result
to a specified power (expression, power) power (dfloat) (dfloat)
 Name Description Arguments Output
Rand Return a psuedo random integer between 0 and - result
232-1 (uint32)
Random Returns a random number between 0 232-1 - result
(uint32)
Sin Calculates the trigonometric sine of an angle number (dfloat) result
(dfloat)
Sinh Calculates the hyperbolic sine of an expression number (dfloat) result
(dfloat)
Sqrt Calculates the square root of a number number (dfloat) result
(dfloat)
Tan Calculates the trigonometric tangent of an angle number (dfloat) result
(dfloat)
Tanh Calculates the hyperbolic tangent of an expression number (dfloat) result
(dfloat)

Null handling functions

The following table lists the functions available in the Null Handling category (square brackets indicate
an argument is optional):

Name Description Arguments Output

IsNotNull Returns true when an expression does not any true/false (int8)
evaluate to the null value
IsNull Returns true when an expression evaluates any true/false (int8)
to the null value
MakeNull Change an in-band null to out of band null any (column) -
string (string)
NullToEmpty Returns an empty string if input column is input column input column value
null, otherwise returns the input column or empty string
value
NullToZero Returns zero if input column is null, input column input column value
otherwise returns the input column value or zero
 Name Description Arguments Output
NullToValue Returns specified value if input column is input column, input column value
null, otherwise returns the input column value or value
value
SetNull Assign a null value to the target column - -

true = 1

false = 0

Number functions
The following table lists the functions available in the Number category (square brackets indicate an
argument is optional):

Name Description Arguments Output

MantissaFromDecimal Returns the mantissa from the given number result
decimal (decimal) (dfloat)
MantissaFromDFloat Returns the mantissa from the given number (dfloat) result
dfloat (dfloat)

Raw functions
The following table lists the functions available in the Raw category (square brackets indicate an
argument is optional):

Name Description Arguments Output

RawLength Returns the length of a raw string input string (raw) Result (int32)

String functions
The following table lists the functions available in the String category (square brackets indicate an
argument is optional):

Name Description Arguments Output

AlNum Return whether the given string consists string (string) true/false
of alphanumeric characters (int8)
Alpha Returns 1 if string is purely alphabetic string (string) result
(int8)
CompactWhiteSpace Return the string after reducing all string (string) result
consective whitespace to a single space (string)
Compare Compares two strings for sorting string1 (string) result
(int8)
string2 (string)

[justification (L or
R)]
ComparNoCase Case insensitive comparison of two string1 (string) result
strings string2 (string) (int8)
ComparNum Compare the first n characters of the string1 (string) result
two strings string2 (string) (int8)
length (int16)
CompareNumNoCase Caseless comparison of the first n string1 (string) result
characters of the two strings string2 (string) (int8)
length (int16)
Convert Converts specified characters in a string fromlist (string) result
to designated replacement characters (string)
tolist (string)

expression (string)
Count Count number of times a substring string (string) result
occurs in a string substring (string) (int32)
Dcount Count number of delimited fields in a string (string) result
string delimiter (string) (int32)
DownCase Change all uppercase letters in a string string (string) result
to lowercase (string)
DQuote Enclose a string in double quotation string (string) result
marks (string)
 Name Description Arguments Output
Field Return 1 or more delimited substrings string (string) result
delimiter (string) (string)
occurrence (int32)
[number (int32)]
Index Find starting character position of string (string) result
substring substring (string) (int32)
occurrence (int32)
Left Leftmost n characters of string string (string) result
number (int32) (string)
Len Length of string in characters string (string) result
(int32)
Num Return 1 if string can be converted to a string (string) result
number (int8)
PadString Return the string padded with the string (string) result
optional pad character and optional padlength (int32) (string)
length
Right Rightmost n characters of string string (string) result
number (int32) (string)
Soundex Returns a string which identifies a set of string (string) result
words that are (roughly) phonetically (string)
alike based on the standard, open
algorithm for SOUNDEX evaluation
Space Return a string of N space characters length (int32) result
(string)
Squote Enclose a string in single quotation string (string) result
marks (string)
Str Repeat a string string (string) result
repeats (int32) (string)
StripWhiteSpace Return the string after stripping all string (string) result
whitespace from it (string)
Trim Remove all leading and trailing spaces string (string) result
and tabs plus reduce internal [stripchar (string)] (string)
occurrences to one [options (string)]
TrimB Remove all trailing spaces and tabs string (string) result
(string)
 Name Description Arguments Output
TrimF Remove all leading spaces and tabs string (string) result
(string)
Trim Leading Returns a string with leading and string (string) result
Trailing trailing whitespace removed (string)
Upcase Change all lowercase letters in a string string (string) result
to uppercase (string)

true = 1

false = 0

Possible options for the Trim function are:

 L Removes leading occurrences of character.

 T Removes trailing occurrences of character.
 B Removes leading and trailing occurrences of character.
 R Removes leading and trailing occurrences of character, and reduces multiple
occurrences to a single occurrence.
 A Removes all occurrences of character.
 F Removes leading spaces and tabs.
 E Removes trailing spaces and tabs.
 D Removes leading and trailing spaces and tabs, and reduces multiple spaces and tabs to
single ones.

Parent topic: Functions used in IBM WebSphere DataStage and QualityStage

Vector function
The following function can be used within expressions to access an element in a vector column. The
vector index starts at 0.

Name Description Arguments Output

ElementAt Accesses an element of a vector input column index (int) element of vector
This can be used as part of, or the whole of an expression. For example, an expression to add 1 to
the third element of an vector input column 'InLink.col1' would be:

ElementAt(InLink.col1, 2) + 1

Type Conversion Functions

The following table lists the functions available in the Type Conversion category (square brackets
indicate an argument is optional):

Name Description Arguments Output

DateToString Return the string representation of the date result
given date [format (string)] (string)
DecimalToDecimal Returns the given decimal in decimal decimal (decimal) result
representation with specified precision [rtype (string)] (decimal)
and scale [packedflag (int8)]
DecimalToDFloat Returns the given decimal in dfloat number (decimal) result
representation ["fix_zero"] (dfloat)
DecimalToString Return the string representation of the number (decimal) result
given decimal ["fix_zero"] (string)
DfloatToDecimal Returns the given dfloat in decimal number (dfloat) result
representation [rtype (string)] (decimal)
DfloatToStringNoExp Returns the given dfloat in its string number (dfloat) result
representation with no exponent, using scale (string) (string)
the specified scale
IsValid Return whether the given string is valid type (string) result
for the given type. Valid types are format (string) (int8)
"date", "decimal", "dfloat", "sfloat",
"int8", "uint8", "int16", "uint16",
"int32", "uint32", "int64", "uint64",
"raw", "string", "time", "timestamp".
"ustring"
StringToDate Returns a date from the given string in date (string) date
the given format format (string)
StringToDecimal Returns the given string in decimal string (string) result
representation [rtype (string)] (decimal)
StringToRaw Returns a string in raw representation string (string) result
(raw)
 Name Description Arguments Output
StringToTime Returns a time representation of the string (string) time
given string [format (string)]
StringToTimestamp Returns a timestamp representation of string (string) timestamp
the given string [format (string)]
TimestampToDate Returns a date from the given timestamp date
timestamp
TimestampToString Return the string representation of the timestamp [format result
given timestamp (string)] (string)
TimestampToTime Returns the time from a given timestamp time
timestamp
TimeToString Return the string representation of the time [format result
given time (string)] (string)
StringToUstring Returns a ustring from the given string, string (string) result
optionally using the specified map [,mapname (ustring)
(otherwise uses project default) (string)]
UstringToString Returns a string from the given ustring, string(ustring) result
optionally using the specified map [,mapname (string)
(otherwise uses project default) (string)]

Rtype. The rtype argument is a string, and should contain one of the following:

 ceil. Round the source field toward positive infinity. E.g, 1.4 -> 2, -1.6 -> -1.
 floor. Round the source field toward negative infinity. E.g, 1.6 -> 1, -1.4 -> -2.
 round_inf. Round or truncate the source field toward the nearest representable value,
breaking ties by rounding positive values toward positive infinity and negative values
toward negative infinity. E.g, 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2.
 trunc_zero. Discard any fractional digits to the right of the rightmost fractional digit
supported in the destination, regardless of sign. For example, if the destination is an
integer, all fractional digits are truncated. If the destination is another decimal with a
smaller scale, round or truncate to the scale size of the destination decimal. E.g, 1.6 -> 1,
-1.6 -> -1.

The default is trunc_zero.

Format string. Date, Time, and Timestamp functions that take a format string (e.g.,
timetostring(time, stringformat)) need to have the date format specified. The format strings are
described in Date and time formats.
Where your dates, times, or timestamps convert to or from ustrings, WebSphere DataStage will
pick this up automatically. In these cases the separators in your format string (for example, `:' or
`-') can themselves be Unicode characters.

fix_zero. By default decimal numbers comprising all zeros are treated as invalid. If the string
fix_zero is specified as a second argument, then all zero decimal values are regarded as valid.

Type "casting" functions

There is a special class of type conversion function to help you when performing mathematical
calculations using numeric fields. For example, if you have a calculation using an output column of type
float derived from an input column of type integer in a Parallel Transformer stage the result will be
derived as an integer regardless of its float type. If you want a non-integral result for a calculation using
integral operands, you can use the following functions (which act in a similar way as casting in C) to cast
the integer operands into non-integral operands:

Name Description Arguments Output

AsDouble Treat the given number as a double number (number) number (double)
AsFloat Treat the given number as a float number (number) number (float)
AsInteger Treat the given number as an integer number (number) number (int)

Utility functions
The following table lists the functions available in the Utility category (square brackets indicate an
argument is optional):

Name Description Arguments Output

GetEnvironment Returns the value of the given environment environment result
variable variable (string) (string)
NextSKChain Returns the value of the surrogate key column value (number) result
for the next record in the chain, or value for (int64)
the newest record
NextSurrogateKey Returns the value of the next surrogate key None result
(int64)
 Name Description Arguments Output
PrevSKChain Returns the value of the surrogate key column value (number) result
for the previous record in the chain, or value (int64)
for the first record