IBM Support

JR42002: DataStage parallel transformer function DateOffsetByComponents does not provide correct output.


You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • This APAR is about doing arithmetic on dates. The issue lies in
    the definition of a "month". A Gregorian month can have
    different numbers of days (28, 29, 30, and 31). The inconsistent
    length of a month introduces issues when we add or subtract to
    months to a date.
    The transformer function DateOffsetByComponents and
    TimeStampOffsetByComponents follows this rule:
    If applying an operation to a date yields an invalid date,
    adjust the date to make it valid. So if we have the following
    2011 January 1 + 1 Month = 2011 February 31 (INVALID!) = 2011
    February 28 + 3 Days = 2011 March 3 (RESULT)
    2011 March 31 - 1 Month = 2011 February 31 (INVALID!) = 2011
    February 28 + 3 Days = 2011 March 3 (RESULT)
    This logic to make sure that both transformer functions always
    returns a valid data has been in code for a long time. However,
    some customers prefer the following behavior:
    2011 January 1 + 1 Month = 2011 January 31 + 28 Days (February
    has 28 days for this particular year) = 2011 February 28
    2011 March 31 - 1 Month = 2011 March 31 - 31 Days (March always
    has 31 days) = 2011 February 2011 (RESULT)
    The same issue can come up when adding years to a date. So
    both transformer functions modifies the year, and then
    checks the validity of the result, adjusting date accordingly.
    Some customers want to modify the number of years (365 or 366
    days depending on year) to get the result.
    The code changes for this APAR change the behavior of the
    DateOffsetByComponents and TimestampOffsetByComponents
    transformer functions.  The effect of the changes is different
    for different versions of Information Server.
    For versions before IS 9.1, the patch does not alter the default
    behavior of the transformer functions. The default behavior is
    2011 January 31 + 1 Month equals to 2011 March 3. The patch
    introduces an environment variable called
    APT_DATE_ADD_NO_ROLLOVER. When this environment variable is set,
    the result is that 2011 January 31 + 1 Month equals to 2011
    February 28.
    For version 9.1 and later, the default behavior of
    DateOffsetByComponents and TimestampOffsetByComponents
    are different from (unpatched)
    earlier versions. This behavior matches the behavior of most SQL
    databases.  The results of January 31, 2011 + 1 Month equals to
    2011 February 28.  Therefore, for version 9.1,
    APT_DATE_ADD_NO_ROLLOVER will not change any behavior.
    In its place, for version 9.1 and later introduces a new
    environment variable which restores the pre-9.1 behavior:
    When this environment variable is set, the result for 2011
    January 31 + 1 Month equals to 2011 March 3.

Local fix

  • A patch is provided for this issue.
    With the patch installed the DateOffsetByComponents() and
    TimeStampOffsetByComponents() functions will behave differently
    than in earlier versions.
    If the user wants to keep the original behavior for 9.1+, they
    should set the environment variable APT_DATE_ADD_ROLLOVER.

Problem summary

  • When the customer do a date mathby adding months and years, the
    default behavior is to preserve the extra days.  This patch will
    enable the ability to omit the extra days.  For example, on
    January 31, 2011 -- add a month to that will yield February 28,
    2011 plus 3 days.  Legacy behavior will add those days,
    resulting in a value of March 3, 2011.  Using the environment
    variable will drop the extra days leaving us with February 28,

Problem conclusion

  • A patch is provided for this.

Temporary fix

  • There are a few methods that can be done to avoid this issue
    without the patch --
    1.  Use the database logic to handle date math.
    2.  Create a logic within transformer that calculate months, and
    then deduct days dependent on the resulting month.


  • This will become default behavior in future versions of IIS.

APAR Information

  • APAR number


  • Reported component name


  • Reported component ID


  • Reported release


  • Status


  • PE




  • Special Attention


  • Submitted date


  • Closed date


  • Last modified date


  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name


  • Fixed component ID


Applicable component levels

  • R850 PSY


  • R870 PSY


Document information

More support for: InfoSphere DataStage

Software version: 8.5

Reference #: JR42002

Modified date: 13 February 2014

Translate this page: