DSXchange

I have two date column, how do i derive number of months between two dates

First thing would be to define what you mean by 'month'. Second would be to search the forum as this isn't the first time someone asked this question. For example, some answers here.

You have to calculate the difference with a routine.

If you use the DataStage internal date-format the date is a number and you can substract both "number"s and proceed:

For example divide through 30.437500 and round it, round it up, round it down, leave the part after the period.

You can programm what you want, but you need to programm something or you get the routine from somebody who has already programmed such an routine.

Kind regards
Wolfgang

Completed months (always integer) or partial months?

For example, what would be your expected result if the two dates were 2004-01-02 and 2005-03-15 ? Your choices are 14 or 14.4.

A routine is needed to handle the situation where the day number of the first month is larger than the last day of the second month. For example would would be your expected result if the two dates were 2004-01-29 and 2005-02-28 ? February 2005 is complete in this case.

Whatever your specification (your business rule) that's how you code your Routine, and you use the Long Description and comments in the code to explain to future developers that such is the rule being implemented in this case. If you're a contractor, also get your client to sign off agreement that this is how they want "months between two dates" interpreted.