Taskflows > Taskflows > Taskflow functions
  

Taskflow functions

You can use several functions in the Taskflow Expression Editor.
Some of the key functions are described below:
Asset detail functions
You can use the following asset detail functions from the Miscellaneous section of the Expression Editor:
Character functions
You can use the following character functions from the Strings section of the Expression Editor:
Conversion functions
You can use the following conversion functions from the Dates and Times, Miscellaneous, or String sections of the Expression Editor:
Data cleansing functions
You can use the following data cleansing function from the Miscellaneous section of the Expression Editor:
Date functions
You can use the following date functions from the Dates and Times section of the Expression Editor:
Numeric functions
You can use the following numeric function from the Numbers section of the Expression Editor:
Test functions
You can use the following test functions from the Miscellaneous section of the Expression Editor:

addToDate

Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function. The addToDate function accepts positive and negative integer values. Use addToDate to change the following parts of a date:

Syntax

date:addToDate(xs:dateTime('date'), 'format', amount)
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values that you want to change.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to change. Enclose the format string within single quotation marks, for example, 'mm'. The format string is not case sensitive.
amount
Required
An integer value that specifies the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid transformation expression that evaluates to an integer.

Return Value

Date in the same format as the date you pass to this function.
NULL if a null value is passed as an argument to the function.

Examples

The following expressions all add one month to each date in the DATE_SHIPPED column. If you pass a value that creates a day that does not exist in a particular month, addToDate returns the last day of the month. For example, if you add one month to Jan 31 1998, addToDate returns Feb 28 1998.
Also, addToDate recognizes leap years and adds one month to Jan 29 2000:
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MM', 1)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MON', 1)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MONTH', 1)
The following table shows some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 12 1998 12:00:30AM
Feb 12 1998 12:00:30AM
Jan 31 1998 6:24:45PM
Feb 28 1998 6:24:45PM
Jan 29 2000 5:32:12AM
Feb 29 2000 5:32:12AM  (Leap Year)
Oct 9 1998 2:30:12PM
Nov 9 1998 2:30:12PM
NULL
NULL
The following expressions subtract 10 days from each date in the DATE_SHIPPED column:
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'D', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DD', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DDD', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DY', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DAY', -10)
The following table shows some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 22 1996 12:00AM
Jan 31 1997 6:24:45PM
Jan 21 1997 6:24:45PM
Mar 9 1996 5:32:12AM
Feb 29 1996 5:32:12AM  (Leap Year)
Oct 9 1997 2:30:12PM
Sep 30 1997 2:30:12PM
Mar 3 1996 5:12:20AM
Feb 22 1996 5:12:20AM
NULL
NULL
The following expressions subtract 15 hours from each date in the DATE_SHIPPED column:
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH', -15)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH12', -15)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH24', -15)
The following table shows some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM
Jan 31 1997 6:24:45PM
Jan 31 1997 3:24:45AM
Oct 9 1997 2:30:12PM
Oct 8 1997 11:30:12PM
Mar 3 1996 5:12:20AM
Mar 2 1996 2:12:20PM
Mar 1 1996 5:32:12AM
Feb 29 1996 2:32:12PM  (Leap Year)
NULL
NULL

dateDiff

Returns the length of time between two dates. You can specify the format as years, months, days, hours, minutes, seconds, milliseconds, or microseconds. The dateDiff function subtracts the second date from the first date and returns the difference.
The dateDiff function calculates the value based on the number of months instead of the number of days. It calculates the date differences for partial months with the days selected in each month. To calculate the date difference for the partial month, dateDiff adds the days used within the month. It then divides the value with the total number of days in the selected month.
The dateDiff function gives a different value for the same period in the leap year period and a non-leap year period. The difference occurs when February is part of the dateDiff function. The dateDiff function divides the days with 29 for February for a leap year and 28 if it is not a leap year.
For example, you want to calculate the number of months from September 13 to February 19. In a leap year period, dateDiff calculates the month of February as 19/29 months or 0.655 months. In a non-leap year period, dateDiff calculates the month of February as 19/28 months or 0.678 months. The dateDiff function similarly calculates the difference in the dates for the remaining months and the dateDiff function returns the total value for the specified period.
Note: Some databases might use a different algorithm to calculate the difference in dates.

Syntax

date:dateDiff(xs:dateTime('date'), xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values for the first date that you want to compare.
You can enter any valid transformation expression.
date
Required
Date/Time data type. Passes the values for the second date that you want to compare.
You can enter any valid transformation expression.
format
Required
Format string that specifies the date or time measurement. You can specify years, months, days, hours, minutes, seconds, milliseconds, or microseconds. You can specify only one part of the date, such as 'mm'. Enclose the format strings within single quotation marks. The format string is not case sensitive. For example, the format string 'mm' is the same as 'MM', 'Mm', or 'mM'.

Return Value

Double value. If the first date is later than the second date, the return value is a positive number. If the first date is earlier than the second date, the return value is a negative number.
0 if the dates are the same.
NULL if one (or both) of the date values is NULL.

Examples

The following expressions return the number of hours between the DATE_PROMISED and DATE_SHIPPED columns:
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH12')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH24')
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:00AM
Mar 29 1997 12:00:00PM
-2100
Mar 29 1997 12:00:00PM
Jan 1 1997 12:00:00AM
2100
NULL
Dec 10 1997 5:55:10PM
NULL
Dec 10 1997 5:55:10PM
NULL
NULL
Jun 3 1997 1:13:46PM
Aug 23 1996 4:20:16PM
6812.89166666667
Feb 19 2004 12:00:00PM
Feb 19 2005 12:00:00PM
-8784
The following expressions return the number of days between the DATE_PROMISED and the DATE_SHIPPED columns:
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'D')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'DD')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'DDD')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'DY')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'DAY')
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:00AM
Mar 29 1997 12:00:00PM
-87.5
Mar 29 1997 12:00:00PM
Jan 1 1997 12:00:00AM
87.5
NULL
Dec 10 1997 5:55:10PM
NULL
Dec 10 1997 5:55:10PM
NULL
NULL
Jun 3 1997 1:13:46PM
Aug 23 1996 4:20:16PM
283.870486111111
Feb 19 2004 12:00:00PM
Feb 19 2005 12:00:00PM
-366
The following expressions return the number of months between the DATE_PROMISED and DATE_SHIPPED columns:
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MM')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MON')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MONTH')
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:00AM
Mar 29 1997 12:00:00PM
-2.91935483870968
Mar 29 1997 12:00:00PM
Jan 1 1997 12:00:00AM
2.91935483870968
NULL
Dec 10 1997 5:55:10PM
NULL
Dec 10 1997 5:55:10PM
NULL
NULL
Jun 3 1997 1:13:46PM
Aug 23 1996 4:20:16PM
9.3290162037037
Feb 19 2004 12:00:00PM
Feb 19 2005 12:00:00PM
-12
The following expressions return the number of years between the DATE_PROMISED and DATE_SHIPPED columns:
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'Y')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'YY')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'YYY')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'YYYY')
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:00AM
Mar 29 1997 12:00:00PM
-0.24327956989247
Mar 29 1997 12:00:00PM
Jan 1 1997 12:00:00AM
0.24327956989247
NULL
Dec 10 1997 5:55:10PM
NULL
Dec 10 1997 5:55:10PM
NULL
NULL
Jun 3 1997 1:13:46PM
Aug 23 1996 4:20:16PM
0.77741801697531
Feb 19 2004 12:00:00PM
Feb 19 2005 12:00:00PM
-1
The following expressions return the number of months between the DATE_PROMISED and DATE_SHIPPED columns:
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MM')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MON')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'MONTH')
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
LEAP YEAR VALUE
(in Months)
NON-LEAP YEAR VALUE
(in Months)
Sept 13
Feb 19
-5.237931034
-5.260714286
NULL
Feb 19
NULL
N/A
Sept 13
NULL
NULL
N/A

decode

Searches a column for a value that you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a decode function.
If you use decode to search for a value in a string column, you can either trim trailing blank characters with the rtrim function or include the blanks in the search string.

Syntax

util:decode(value, search1, result1, args, default)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Passes the values that you want to search.
You can enter any valid transformation expression. You can pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
search1
Required
Passes the values for which you want to search.
You can enter any valid transformation expression. You can pass any value with the same data type as the value argument. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive.
For example, if you want to search for the string 'Halogen Flashlight' in a particular column, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value.
To pass a NULL value, you must specify an empty sequence in the following format: ()
result1
Required
The value that you want to return if the search finds a matching value.
You can enter any valid transformation expression and pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
args
Required
Pairs of search values and result values separated by a comma.
For example, use the following syntax:
util:decode(value, search1, result1, search2, result2, searchn, resultn, default)
To pass a NULL value, you must specify an empty sequence in the following format: ()
default
Required
The value that you want to return if the search does not find a matching value.
You can enter any valid transformation expression and pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()

Return Value

result1 if the search finds a matching value.
default value if the search does not find a matching value.
NULL if you omit the default argument and the search does not find a matching value.
Even if multiple conditions are met, decode returns the first matching result.

decode and data types

When you use decode, the data type of the return value is always the same as the data type of the result with the greatest precision.
For example, you have the following expression:
util:decode( CONST_NAME
         'Five', 5,
         'Pythagoras', 1.414213562,
         'Archimedes', 3.141592654,
         'Pi', 3.141592654 )
The return values in this expression are 5, 1.414213562, and 3.141592654. The first result is an integer, and the other results are decimal. The decimal data type has a greater precision than the integer data type. This expression always writes the result as a decimal value.
You cannot create a decode function with both string and numeric return values.
For example, the following expression is not valid:
util:decode( CONST_NAME
         'Five', 5,
         'Pythagoras', '1.414213562',
         'Archimedes', '3.141592654',
         'Pi', 3.141592654 )
When you validate the expression above, you receive the following error message:
Function cannot resolve operands of ambiguously mismatching datatypes.

Examples

You might use decode in an expression that searches for a particular ITEM_ID and returns the ITEM_NAME:
util:decode( ITEM_ID, 10, 'Flashlight',
                 14, 'Regulator',
                 20, 'Knife',
                 40, 'Tank',
                 'NONE' )
The following table lists some sample values and return values:
ITEM_ID  
RETURN VALUE
10
Flashlight
14
Regulator
17
NONE
20
Knife
25
NONE
NULL
NONE
40
Tank
The decode function returns the default value of NONE for items 17 and 25 because the search values did not match the ITEM_ID. Also, decode returns NONE for the NULL ITEM_ID.
The following expression tests multiple columns and conditions, evaluated in a top to bottom order for TRUE or FALSE:
util:decode( TRUE,
        Var1 = 22, 'Variable 1 was 22!',
        Var2 = 49, 'Variable 2 was 49!',
        Var1 < 23, 'Variable 1 was less than 23.',
        Var2 > 30, 'Variable 2 was more than 30.',
        'Variables were out of desired ranges.')
The following table lists some sample values and return values:
Var1   
Var2   
RETURN VALUE
21
47
Variable 1 was less than 23.
22
49
Variable 1 was 22!
23
49
Variable 2 was 49!
24
27
Variables were out of desired ranges.
25
50
Variable 2 was more than 30.

getAssetLocation

Returns the location where the taskflow that uses the function is stored.
For example, you can use the function to find the taskflow location for debugging purposes.

Syntax

util:getAssetLocation()
The getAssetLocation function does not use an argument.

Return Value

Location where the taskflow that uses the function is stored.

Example

If you use the getAssetLocation function in a taskflow that is stored under Default\Orders, the function returns the following value:
Default\Orders

getAssetName

Returns the name of the taskflow that uses the function.
For example, you can use the function in a Notification Task step to include the taskflow name in a taskflow failure email notification that you send to stakeholders.

Syntax

util:getAssetName()
The getAssetName function does not use an argument.

Return Value

Name of the taskflow that uses the function.

Example

If you use the getAssetName function in a taskflow that is named Order Management, the function returns the following value:
Order Management

getDatePart

Returns the specified part of a date as an integer value. Therefore, if you create an expression that returns the month portion of the date, and pass a date such as Apr 1 1997 00:00:00, getDatePart returns 4.

Syntax

date:getDatePart(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to return. Enclose format strings within single quotation marks, for example, 'mm'. The format string is not case sensitive.
For example, if you pass the date Apr 1 1997 to getDatePart, the format strings 'Y', 'YY', 'YYY', or 'YYYY' all return 1997.

Return Value

Integer representing the specified part of the date.
NULL if a value passed to the function is NULL.

Examples

The following expressions return the hour for each date in the DATE_SHIPPED column. 12:00:00AM returns 0 because the default date format is based on the 24 hour interval:
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'HH')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'HH12')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'HH24')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
0
Sep 2 1997 2:00:01AM
2
Aug 22 1997 12:00:00PM
12
June 3 1997 11:30:44PM
23
NULL
NULL
The following expressions return the day for each date in the DATE_SHIPPED column:
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'D')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'DD')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'DDD')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'DY')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'DAY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
13
June 3 1997 11:30:44PM
3
Aug 22 1997 12:00:00PM
22
NULL
NULL
The following expressions return the month for each date in the DATE_SHIPPED column:
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'MM')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'MON')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'MONTH')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
3
June 3 1997 11:30:44PM
6
NULL
NULL
The following expressions return the year for each date in the DATE_SHIPPED column:
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'Y')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'YY')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'YYY')
date:getDatePart(xs:dateTime('DATE_SHIPPED'), 'YYYY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
1997
June 3 1997 11:30:44PM
1997
NULL
NULL

getInstanceStartTime

Returns the start date and start time of the running instance of the taskflow that uses the function.
For example, you can use the function to find when a taskflow started running and abort the taskflow if it has been running beyond the expected duration.

Syntax

util:getInstanceStartTime()
The getInstanceStartTime function does not use an argument.

Return Value

Start date and start time of the running instance of the taskflow that uses the function.
The return value is in the Coordinated Universal Time (UTC) format as follows:
YYY-MM-DDTHH:mm:ss.sssZ

Example

If you use the getInstanceStartTime function in a taskflow that was started on January 19, 2021, the function returns the following value:
2021-01-19T10:11:21.047Z

iif

Returns one of two values that you specify based on the results of a condition.

Syntax

util:iif(condition, val1 ,val2)
The following table describes the arguments:
Argument
Required/Optional
Description
condition
Required
The condition that you want to evaluate.
You can enter any valid transformation expression that evaluates to TRUE or FALSE.
val1
Required
The value that you want to return if the condition is TRUE. The return value is always the data type specified by this argument.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
val2
Optional
The value that you want to return if the condition is FALSE.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
The FALSE (val2) condition in the iif function is not required. If you omit val2, the function returns one of the following values when the condition is FALSE:
For example, the following expression does not include a FALSE condition and val1 is a string data type so decode returns an empty string for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
'' (empty string)
120
Sally Green
Sally Green
NULL
Greg Jones
'' (empty string)

Return Value

val1 if the condition is TRUE.
val2 if the condition is FALSE.
For example, the following expression includes the FALSE condition NULL so decode returns NULL for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME, NULL)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
NULL
120
Sally Green
Sally Green
NULL
Greg Jones
NULL

iif and data types

When you use iif, the data type of the return value is the same as the data type of the result with the greatest precision.
For example, you have the following expression:
util:iif(SALES < 100, 1, .3333)
The TRUE result (1) is an integer and the FALSE result (.3333) is a decimal. The Decimal data type has a greater precision than the Integer data type. Therefore, the data type of the return value is always a decimal value.

Special uses of iif

Use nested iif statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is 0 or negative:
util:iif(SALES > 0, util:iif(SALES < 50, SALARY1, util:iif(SALES < 100, SALARY2, util:iif( SALES < 200, SALARY3, BONUS))), 0 )

in

Matches input data to a list of values. By default, the match is case sensitive.

Syntax

util:in(valueToSearch, values, caseFlag)
The following table describes the arguments:
Argument
Required/
Optional
Description
valueToSearch
Required
Can be a string, date, or numeric value. Input value that you want to match against a comma-separated list of values.
To pass a NULL value, you must specify an empty sequence in the following format: ()
values
Required
Can be a string, date, or numeric value. Comma-separated list of values that you want to search for. Values can be columns. There is no limit to the maximum number of values that you can list.
To pass a NULL value, you must specify an empty sequence in the following format: ()
caseFlag
Optional
Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression.
When caseFlag is a number other than 0, the function is case sensitive.
When caseFlag is a null value or 0, the function is not case sensitive.

Return Value

TRUE (1) if the input value matches the list of values.
FALSE (0) if the input value does not match the list of values.
NULL if the input is a null value.

Example

The following expression determines if the input value is a safety knife, chisel point knife, or medium titanium knife. The input values do not have to match the case of the values in the comma-separated list:
util:in(ITEM_NAME, ‘Chisel Point Knife’, ‘Medium Titanium Knife’, ‘Safety Knife’, 0)
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Stabilizing Vest
0 (FALSE)
Safety knife
1 (TRUE)
Medium Titanium knife
1 (TRUE)
NULL

instr

Returns the position of a character set in a string, counting from left to right.

Syntax

sff:instr(str, search, start, occurrence, comparison_type)
The following table describes the arguments:
Argument
Required/Optional
Description
str
Required
Passes the value that you want to evaluate.
The string must be a character string. You can enter any valid transformation expression.
The results of the expression must be a character string. Otherwise, instr converts the value to a string before evaluating it.
search
Required
The set of characters that you want to search for.
You can enter any valid transformation expression. If you want to search for a character string, enclose the characters that you want to search for within single or double quotation marks.
The value must match a part of the string. For example, if you use instr('Alfred Pope', 'Alfred Smith') the function returns 0.
The value is case sensitive.
start
Optional
The position in the string where you want to start the search. You can enter any valid transformation expression. The value must be an integer.
The default is 1, meaning that instr starts the search at the first character in the string.
If the start position is 0, instr searches from the first character in the string. If the start position is a positive number, instr locates the start position by counting from the beginning of the string. If the start position is a negative number, instr locates the start position by counting from the end of the string. If you omit this argument, the function uses the default value of 1.
occurrence
Optional
You can enter any valid transformation expression. If the search value appears more than once in the string, you can specify which occurrence you want to search for. For example, you would enter 2 to search for the second occurrence from the start position.
You can enter a positive integer that is greater than 0.
If you omit this argument, the function uses the default value of 1, which means that instr searches for the first occurrence of the search value. If you pass a decimal value, the function rounds it to the nearest integer value. If you pass a negative integer or 0, the function is not valid.
comparison_type
Optional
The string comparison type, either linguistic or binary.
Linguistic comparisons take language-specific collation rules into account, while binary comparisons perform bitwise matching. For example, the German sharp s character matches the string “ss” in a linguistic comparison, but not in a binary comparison. Binary comparisons run faster than linguistic comparisons.
You must enter one of the following integer values:
  • - 0: instr performs a linguistic string comparison.
  • - 1: instr performs a binary string comparison.
Default is 0.

Return Value

Integer if the search is successful. Integer represents the position of the first character in the search argument, counting from left to right.
0 if the search is unsuccessful.
NULL if a value passed to instr is NULL.

Examples

The following expression returns the position of the first occurrence of the letter ‘a’, starting from the beginning of each company name:
sff:instr( COMPANY, 'a' )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
13
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop
3
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns the position for the ‘a’ in ‘Aqua’.
The following expression returns the position of the second occurrence of the letter ‘a’, starting from the beginning of each company name:
sff:instr( COMPANY, 'a', 1, 2 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
0
Maco Shark Shop
8
Scuba Gear
9
Frank's Dive Shop
0
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns 0.
The following expression returns the position of the second occurrence of the letter ‘a’ in each company name, starting from the last character in the company name.
sff:instr( COMPANY, 'a', -1, 2 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
0
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop
0
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in 'Blue Fin Aqua Center’, and returns 0.
The following expression returns the position of the first character in the string ‘Blue Fin Aqua Center’, starting from the last character in the company name:
sff:instr( COMPANY, 'Blue Fin Aqua Center', -1, 1 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
1
Maco Shark Shop
0
Scuba Gear
0
Frank's Dive Shop
0
VIP Diving Club
0

isNull

Returns whether a value is NULL.

Syntax

util:isNull(value)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
value
Required
Passes the rows that you want to evaluate. You can enter any valid transformation expression. You can pass a value of any data type except Binary.

Return Value

TRUE (1) if the value is NULL.
FALSE (0) if the value is not NULL.

Example

The following example checks for NULL values in the items table:
util:isNull( ITEM_NAME )
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
0 (FALSE)
NULL
1 (TRUE)
Regulator system
0 (FALSE)
''
0 (FALSE)  Empty string is not NULL

lastDay

Returns the date of the last day of the month for each date in a column.

Syntax

date:lastDay(date)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time data type. Passes the date for which you want to return the last day of the month.
You can enter any valid transformation expression that evaluates to a date.

Return Value

Date. The last day of the month for the date value that you pass to this function.
NULL if a value in the selected column is NULL.

Example

The following expression returns the current date as the last day:
date:lastDay(fn:current-dateTime('DATE'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
18-04-98 01:00
Apr 18 1998 01:00:00 AM
20-08-99 05:00
Aug 20 1999 05:00:00 AM
The following expression returns the last day of the previous month for each date in the DATE column:
date:lastDay(date:addToDate(fn:current-dateTime('DATE','MM',-1))
The following table lists some sample values and return values:
DATE
RETURN VALUE
Apr 1 1998 12:00:00AM
Mar 31 1998 12:00:00AM
Jan 6 1998 12:00:00AM
Dec 31 1997 12:00:00AM
Feb 2 1996 12:00:00AM
Jan 31 1996 12:00:00AM
NULL
NULL
You can nest toDate to convert string values to a date. toDate function always includes time information. If you pass a string that does not have a time value, the date returned will include the time 00:00:00.
The following example returns the last day of the month for each date in the same format as the string:
date:lastDay(toDate('DATE', 'MON-DD-YYYY'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
Nov-30-1998 00:00:00
'28-APR-98'
Apr-30-1998 00:00:00
NULL
NULL
'18-FEB-96'
Feb-29-1996 00:00:00(Leap year)
date:lastDay(date:toDate("DATE", "YYYY-MM-DD"))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
1998-Nov-30 00:00:00
'28-APR-98'
1998-Apr-30 00:00:00
NULL
NULL
'18-FEB-96'
1996-Feb-29 00:00:00(Leap year)

lpad

Adds a set of blank characters to the beginning of a string to set the string to a specified length.

Syntax

sff:lpad(first_string, length, second_string)
The following table describes the arguments:
Argument
Required/
Optional
Description
first_string
Required
Can be a character string. Passes the string that you want to change. You can enter any valid transformation expression.
To pass a NULL value, you must specify an empty sequence in the following format: ()
length
Required
Must be a positive integer literal. This argument specifies the length that you want for each string. When length is a negative number, lpad returns NULL.
second_string
Optional
Can be any string value. The characters that you want to append to the left-side of the first_string values. You can enter any valid transformation expression. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blank characters.
To pass a NULL value, you must specify an empty sequence in the following format: ()

Return Value

String of the specified length.
NULL if a value passed to the function is NULL or if length is a negative number.

Example

The following expression standardizes numbers to six digits by padding them with leading zeros.
sff:lpad(PART_NUM, 6, '0')
The following table lists some sample values and return values:
PART_NUM
RETURN VALUE
702
000702
1
000001
0553
000553
484834
484834
lpad counts the length from left to right. If the first string is longer than the length, lpad truncates the string from right to left. For example, lpad(‘alphabetical’, 5, ‘x’) returns the string ‘alpha’.
If the second string is longer than the total characters needed to return the specified length, lpad uses a portion of the second string:
sff:lpad(ITEM_NAME, 16, '*..*')
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
*..**.Flashlight
Compass
*..**..**Compass
Regulator System
Regulator System
Safety Knife
*..*Safety Knife
The following expression shows how lpad handles negative values for the length argument for each row in the ITEM_NAME column:
sff:lpad(ITEM_NAME, -5, '.')
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
NULL
Compass
NULL
Regulator System
NULL

ltrim

Removes leading spaces or characters from the beginning of a string.
If you do not specify the trim_set argument in the expression, ltrim removes both single-byte and double-byte spaces from the beginning of a string.
If you use ltrim to remove characters from a string, ltrim compares the trim_set to each character in the str argument, character-by-character, starting from the left side of the string. If the character in the string matches any character in the trim_set, ltrim removes it. The ltrim function continues comparing and removing characters until it fails to find a matching character in the trim_set. Then it returns the string, which does not include matching characters.

Syntax

sff:ltrim(str, trim_set)
The following table describes the arguments:
Arguments
Required/
Optional
Description
str
Required
Any string value. Passes the strings that you want to modify. You can enter any valid transformation expression. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
trim_set
Optional
Any string value. Passes the characters that you want to remove from the beginning of the first string. You can enter any valid transformation expression. You can also enter a character string.
You must enclose the trim set value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The ltrim function is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'.

Return Value

String. The string values with the specified characters in the trim_set argument removed.
NULL if a value passed to ltrim is NULL. If the trim_set is NULL, ltrim returns NULL.

Example

The following expression removes the characters ‘S’ and ‘.’ from the strings in the LAST_NAME column:
sff:ltrim( LAST_NAME, 'S.')
The following table lists some sample values and return values:
LAST_NAME
RETURN VALUE
Nelson
Nelson
Osborne
Osborne
NULL
NULL
S. MacDonald
MacDonald
Sawyer
awyer
H. Bender
H. Bender
Steadman
teadman
The ltrim function removes ‘S.’ from S. MacDonald and the ‘S’ from both Sawyer and Steadman, but not the period from H. Bender. This is because ltrim searches, character-by-character, for the set of characters you specify in the trim_set argument. If the first character in the string matches the first character in the trim_set, ltrim removes it. Then, ltrim looks at the second character in the string. If it matches the second character in the trim_set, ltrim removes it, and so on. When the first character in the string does not match the corresponding character in the trim_set, ltrim returns the string and evaluates the next row.
In the example of H. Bender, H does not match either character in the trim_set argument, so ltrim returns the string in the LAST_NAME column and moves to the next row.

Tips for ltrim

Use ltrim with CONCAT to remove leading blank spaces after you concatenate two strings.
You can also remove multiple sets of characters by nesting ltrim. For example, to remove leading blank spaces and the character 'T' from a column of names, you might create an expression as follows:
sff:ltrim( sff:ltrim( NAMES ), 'T' )

round (Numbers)

Rounds numbers to a specified number of digits or decimal places. You can also use round to round dates.
The Round function behaves as follows:

Syntax

fn:round(arg)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
Must be a numeric data type. You can enter any valid transformation expression. Use operators to perform arithmetic before you round the values.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The function rounds to the nearest integer, truncating the decimal portion of the number. For example, round(12.99) returns 13 and round(15.20) returns 15.

Return Value

Numeric value.
If one of the arguments is NULL, round returns NULL.
Example 1. Example
The following expression returns the rounded values in the Price column.
fn:round(PRICE)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.99
13.0
-15.99
-16.0
-18.99
-19.0
56.95
57.0
NULL
NULL
If you want to be more specific with the precision and round the number to the nearest integer or truncate the decimal portion, it is recommended to use round-half-to-even function.
fn:round-half-to-even(arg, precision)
If you enter a positive precision, the function rounds to this number of decimal places. For example, round(12.99, 1) returns 13.0 and round(15.44, 1) returns 15.4.
If you enter a negative precision, the function rounds this number of digits to the left of the decimal point, returning an integer. For example, round(12.99, -1) returns 10 and rounds(15.99, -1) returns 20.
The value returned is the nearest, that is, numerically closest value to the argument that is a multiple of ten to the power of minus precision. If two such values are equally near, that is, if the fractional part in the argument is exactly .500..., the function returns the one whose least significant digit is even.

Example

The following expression returns the values in the Price column rounded to three decimal places.
fn:round-half-to-even(PRICE, 3)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.9936
12.994
15.9949
15.995
-18.8678
-18.868
56.9561
56.956
NULL
NULL
You can round digits to the left of the decimal point by passing a negative integer in the precision argument:
fn:round-half-to-even(PRICE, -2)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
13242.99
13200.0
1435.99
1400.0
-108.95
-100.0
NULL
NULL
If you pass zero in the precision argument, the function rounds to the nearest integer:
fn:round-half-to-even(PRICE, 0)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.99
13.0
-15.99
-16.0
-18.99
-19.0
56.95
57.0
NULL
NULL

rtrim

Removes blank characters or characters from the end of a string.
If you do not specify a trim_set parameter in the expression, rtrim removes both single-byte and double-byte spaces from the end of a string.
If you use rtrim to remove characters from a string, rtrim compares the trim_set to each character in the string argument, character-by-character, starting with the right side of the string. If the character in the string matches any character in the trim_set, rtrim removes it. The rtrim function continues comparing and removing characters until it fails to find a matching character in the trim_set. It returns the string without the matching characters.

Syntax

sff:rtrim(str, trim_set)
The following table describes the arguments:
Argument
Required/
Optional
Description
string
Required
Any string value. Passes the values that you want to trim. You can enter any valid transformation expression. Use operators to perform comparisons or concatenate strings before removing blank characters from the end of a string.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
trim_set
Optional
Any string value. Passes the characters that you want to remove from the end of the string. You can also enter a text literal.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The rtrim function is case sensitive. For example, if you want to remove the 'o' character from the string 'Alfredo', you would enter 'o', not 'O'.

Return Value

String. The string values with the specified characters in the trim_set argument removed.
NULL if a value passed to rtrim is NULL. If the trim_set is NULL, rtrim returns NULL.

Example

The following expression removes the characters ‘re’ from the strings in the LAST_NAME column:
sff:rtrim( LAST_NAME, 're')
The following table lists some sample values and return values:
LAST_NAME
RETURN VALUE
Nelson
Nelson
Page
Pag
Osborne
Osborn
NULL
NULL
Sawyer
Sawy
H. Bender
H. Bend
Steadman
Steadman
The rtrim function removes ‘e’ from Page even though ‘r’ is the first character in the trim_set. This is because rtrim searches, character-by-character, for the set of characters you specify in the trim_set argument. If the last character in the string matches the first character in the trim_set, rtrim removes it. If, however, the last character in the string does not match, rtrim compares the second character in the trim_set. If the second from last character in the string matches the second character in the trim_set, rtrim removes it, and so on. When the character in the string fails to match the trim_set, rtrim returns the string and evaluates the next row.
In the last example, the last character in Nelson does not match any character in the trim_set argument, so rtrim returns the string 'Nelson' and evaluates the next row.

Tips for rtrim

Use rtrim with CONCAT to remove trailing blank characters after you concatenate two strings.
You can also remove multiple sets of characters by nesting rtrim. For example, to remove trailing blank characters and the character ‘t’ from the end of each string in a column of names, you might create an expression similar to the following:
sff:trim( sff:rtrim( NAMES ), 't' )

toChar (Numbers)

Converts numeric values to text strings.

Syntax

sff:toChar(xs:double(val))
Note: After you add the function, you must manually add the phrase (xs:double) in the syntax. Otherwise, the taskflow fails.
The following table describes the arguments:
Argument
Required/ Optional
Description
val
Required
The numeric value that you want to convert to a string. You can enter any valid transformation expression.
toChar converts double values to text strings as follows:
toChar converts decimal values to text strings as follows:

Return Value

String.
NULL if a value passed to the function is NULL.

Double Conversion Example

The following expression converts the double values in the SALES port to strings:
sff:toChar(xs:double(SALES))
SALES
RETURN VALUE
1010.99
'1010.99'
-15.62567
'-15.62567'
10842764968208837340
'1.08427649682088e+019'  (rounded based on the 16th digit and returns the value in scientific notation)
236789034569723
'236789034569723'
0
'0'
33.15
'33.15'
NULL
NULL

Decimal Conversion Example

The following expression converts the decimal values in the SALES port to strings in high precision mode:
sff:toChar(xs:double(SALES))
The following table lists some sample values and return values when the precision is greater than 38:
SALES
RETURN VALUE
2378964536789761
'2378964536789761'
1234567890123456789012345679
'1234567890123456789012345679'
1.234578945469649345876123456
'1.234578945469649345876123456'
0.999999999999999999999999999
'0.999999999999999999999999999'
12345678901234567890123456799
'12345678901234567890123456799'
23456788992233456678458934567123465239
'23456788992233456678458934567123465239'
423456789012345678901234567991234567899
(greater than 38)
'4.23456789012346e+038'
The following table lists some sample values and return values when the precision is greater than 28:
SALES
RETURN VALUE
2378964536789761
'2378964536789761'
1234567890123456789012345679
'1234567890123456789012345679'
1.234578945469649345876123456
'1.234578945469649345876123456'
0.999999999999999999999999999
'0.999999999999999999999999999'
12345678901234567890123456799
(greater than 28)
'1.23456789012346e+028'

toDate

Converts a character string to a Date/Time data type. You use the toDate format strings to specify the format of the source strings.
The output port must be Date/Time for toDate expressions.
If you are converting two-digit years with toDate, use either the RR or YY format string. Do not use the YYYY format string.

Syntax

date:toDate(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Must be a string data type. Passes the values that you want to convert to dates. You can enter any valid transformation expression.
format
Required
Enter a valid toDate format string. The format string must match the parts of the dare argument. For example, if you pass the date 'Mar 15 1998 12:43:10AM', you must use the format string 'MON DD YYYY HH12:MI:SSAM'.

Return Value

Date.
The toDate function always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. You can map the results of this function to any target column with a datetime data type.
NULL if you pass a NULL value to this function.

Examples

date:toDate(xs:dateTime('DATE_PROMISED'), 'MM/DD/YY')
The following table lists some sample values and return values:
DATE_PROMISED
RETURN VALUE
'01/22/98'
Jan 22 1998 00:00:00
'05/03/98'
May 3 1998 00:00:00
'11/10/98'
Nov 10 1998 00:00:00
'10/19/98'
Oct 19 1998 00:00:00
NULL
NULL
date:toDate(xs:dateTime('DATE_PROMISED'), 'MON DD YYYY HH12:MI:SSAM')
The following table lists some sample values and return values:
DATE_PROMISED
RETURN VALUE
'Jan 22 1998 02:14:56PM'
Jan 22 1998 02:14:56PM
'Mar 15 1998 11:11:11AM'
Mar 15 1998 11:11:11AM
'Jun 18 1998 10:10:10PM'
Jun 18 1998 10:10:10PM
'October 19 1998'
Error. Integration Service skips this row.
NULL
NULL
The following expression converts strings in the SHIP_DATE_MJD_STRING port to date values:
date:toDate(xs:dateTime('SHIP_DATE_MJD_STR'), 'J')
The following table lists some sample values and return values:
SHIP_DATE_MJD_STR
RETURN_VALUE
'2451544'
Dec 31 1999 00:00:00.000000000
'2415021'
Jan 1 1900 00:00:00.000000000
Because the J format string does not include the time portion of a date, the return values have the time set to 00:00:00.000000000.
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/RR')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/2005 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YY')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/1905 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/Y')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/8'
04/01/1998 00:00:00.000000000
'08/17/5'
08/17/1995 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YYY')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/998'
04/01/1998 00:00:00.000000000
'08/17/995'
08/17/1995 00:00:00.000000000
The following expression converts strings that includes the seconds since midnight to date values:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YYYY SSSSS')
The following table lists some sample values and return values:
DATE_STR
RETURN_VALUE
'12/31/1999 3783'
12/31/1999 01:02:03
'09/15/1996 86399'
09/15/1996 23:59:59

toDecimal

Converts a string or numeric value to a decimal value. The toDecimal function ignores leading spaces.

Syntax

util:toDecimal(value, scale)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to decimals.
You can enter any valid transformation expression.
scale
Optional
Must be an integer literal between 0 and 28, inclusive. Specifies the number of digits allowed after the decimal point. If you omit this argument, the function returns a value with the same scale as the input value.
You cannot omit this argument in an elastic mapping.

Return Value

Decimal of precision and scale between 0 and 28, inclusive.
0 if the value in the selected column is an empty string or a non-numeric character.
NULL if a value passed to the function is NULL.

Example

This expression uses values from the column IN_TAX. The data type is decimal with precision of 10 and scale of 3:
util:toDecimal(IN_TAX, 3)
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15.679
'60.2'
60.200
'118.348'
118.348
NULL
NULL
'A12.3Grove'
0
This expression uses values from the column Sales. The data type is decimal with precision of 10 and scale of 2:
util:toDecimal(Sales, 2)
The following table lists some sample values and return values:
Sales
RETURN VALUE
'1234'
1234
'1234.01'
1234.01
If you want the return value in 1234.00 format, you can use the following expression:
format-number(util:toDecimal('1234', 2), '0.00')

Decimal overflow

If the size of the number on the left hand side of the decimal point exceeds the precision, the decimal operation overflows.
To resolve this, modify the scale and/or precision of the expression port and connect downstream ports of the mapping to accommodate the size of the input data in the expression.
For example:
If a numeric field is defined to be size 28 with scale of 15, this accepts 13 numbers on the left side of the decimal and 15 on the right. So the following numbers would be valid for a number of 28,15:
1234567890123.11143
13575.123451234567891
However, these numbers would cause a decimal overflow error:
111112222233333.4444
123.1111122222333334

toInteger

Converts a string or numeric value to an integer. The toInteger syntax contains an optional argument that you can choose to round the number to the nearest integer or truncate the decimal portion. The toInteger function ignores leading spaces.

Syntax

util:toInteger(value, flag)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to an integer.
You can enter any valid transformation expression.
flag
Optional
Specifies whether to truncate or round the decimal portion. The flag must be an integer literal or the constants TRUE or FALSE:
  • - toInteger truncates the decimal portion when the flag is TRUE or a number other than 0.
  • - toInteger rounds the value to the nearest integer if the flag is FALSE or 0 or if you omit this argument.

Return Value

Integer.
NULL if a value passed to the function is NULL.
0 if a value passed to the function contains alphanumeric characters.

Example

The following expressions use values from the column IN_TAX:
util:toInteger(IN_TAX, fn:boolean(1))
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15
'60.2'
60
'118.348'
118
NULL
NULL
'A12.3Grove'
0
'    123.87'
123
'-15.6789'
-15
'-15.23'
-15

trunc

Truncates dates to a specific year, month, day, hour, minute, second, or millisecond. You can also use trunc to truncate numbers.
You can truncate the following date parts:

Syntax

date:trunc(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Date/Time data type. The date values that you want to truncate. You can enter any valid transformation expression that evaluates to a date.
To pass a NULL value, you must specify an empty sequence in the following format: ()
format
Required
Enter a valid format string. The format string is not case sensitive.
To pass a NULL value, you must specify an empty sequence in the following format: ()

Return Value

Date.
NULL if a value passed to the function is NULL.

Examples

The following expressions truncate the year portion of dates in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'Y')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'YY')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'YYY')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'YYYY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Jan 1 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jan 1 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Jan 1 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the month portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MM')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MON')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MONTH')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 1 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 1 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 1 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the day portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'D')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'DD')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'DDD')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'DY')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'DAY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 00:00:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the hour portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH12')
date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH24')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:31AM
Jan 15 1998 02:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:00:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:00:00.000000000
NULL
NULL
The following expression truncates the minute portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MI')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 02:10:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:31:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:50:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:29:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:59:00.000000000
NULL
NULL