Determine the month difference between 2 dates

[rajeevs]

Hello All:

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)

Thanks All.

[DSguru2B]

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.

[ray.wurlod]

An "all terms" search for month difference dates returned 17 hits. Somewhere in there lies an answer.

[Madhusv]

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)

Thanks,
Madhu

[ray.wurlod]

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?

[Sainath.Srinivasan]

Just to add to the logic.....you need to check whether the 'dd' parts are different.

i.e. 2001-01-02 to 2001-02-01 is not one month (if my understanding of your requirement is correct)

So you can add
+ (if second_dd > first_dd then -1 else 0)