Thursday, 13 November 2014

Date_Diff Error - Gives Negative Values for SAP Data Services 4.2 (BODI 14.2.2/14.2.3) Chan

Date_Diff problem with SAP BODI Data Services 4.2 - 14.2/14.2.2/14.2.3 (BI Business Objects)

I've found a problem with the values being output in one of my Data Services jobs where date diff was reporting negative numbers. This function compares dates against each other to display an elapsed time.

 Yesterday we upgraded our Data Services to BI4.2 (internally shown as BI 14.2.3) and this has affected jobs using date_diff since the upgrade.

It appears that SAP have changed the function date_diff in  Data Services 14.2.2 (BI4.2) so the date inputs are reversed which means it now gives negative numbers.
The date_diff function compares date1 and date2

 Originally date1 was the later date (end date) and date2 was earlier (start date). As of Data Services 4.2.2 (14.2.2) the first date is the earlier date and date2 was later
Prior to Data Services 14.2.2 the date_diff function works as below:
date_diff(end_date, start_date)
After Data Services 14.2.2 the date_diff function works like this:
date_diff(start_date, _end_date)

This may bring SAP Data Services into line with other products but it really needs to be highlighted to customers as it will break any data services job using date_diff in a job after you upgrade to BODI BI 4.2 or SAP version 14.2.2 or higher

See P41 in the attached document from SAP

No comments:

Post a Comment