JR42002: DataStage parallel transformer function DateOffsetByComponents does not provide correct output.
Fixes are available
Closed as program error.
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 scenario: 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 (RESULT) 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. LOCAL FIX: 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: APT_DATE_ADD_ROLLOVER. When this environment variable is set, the result for 2011 January 31 + 1 Month equals to 2011 March 3. .
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.
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, 2011.
A patch is provided for this.
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.
Reported component name
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fixed component name
Fixed component ID
Applicable component levels