I have to find the difference in month between 2 dates. I know we could do it in SQL, but unfortunately these dates are derived from other columns and hence would need to be done within DataStage. Any help would greatly be appreciated!!
For example:
Date1=2001-01-01
Date2=2004-01-01
Result should be : 36 (months)
You can use DaysSinceFromDate() or convert the dates to Ordinal dates and subtract them. Both will give you number of days between the two dates. Then divide the number by 30.46 to get a close approximation.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
You can get this by using Date functions and below logic appropriately
Stage1: IF Date1>Date2 Then A Else B
Stage2: IF A then ((GetYear(Date1)-GetYear(Date2))*12+GetMonth(Date1))-GetMonth(Date2) Else ((GetYear(Date2)-GetYear(Date1))*12+GetMonth(Date2))-GetMonth(Date1)
Nice logic. Would have been improved by use of the correct function names, such as MonthFromDate() and YearFromDate() in the Transformer stage. Also, ought you not to compare MonthDayFromDate() values to determine A or B approach, rather than the actual dates?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.