Find Number of years between 2 dates.

[DWH-M]

HI Query Busters,

Can any one help me to find out number of years between 2 dates using transformer stage in Datastage 8.1?

Eg: 1900-03-20 (yyyy-mm-dd) to 2010-11-20 (yyyy-mm-dd)
I need to find out number of years(decimal value like 12.4 years) between the above dates. Leap years between the 2 dates should be considered while calculating.

Thanks in advance.

[ArndW]

The function DaysSinceFromDate will give you the number of days between the two dates. Divide by 365 and you should be good to go. Does the one day, 0.00274 of a year, really make a difference if you round to one decimal place?

[kandyshandy]

Code: Select all

1900-03-20 (yyyy-mm-dd) to 2010-11-20 (yyyy-mm-dd) 

12.4 is an example or the actual difference between the dates given in the example?

Wondering it should be something 110 years 8 months?

Are you expecting 110.8 as output?

[Ravi.K]

Derivation: JulianDayFromDate(Date1) - JulianDayFromDate(Date2)

The result devide by 365 days will give you the difference.

[Sreenivasulu]

Pls do not divide by 365 as you would get icorrect values if a leap year is present. Always use system functions . If not present in 'datastage data functions' use the 'database date functions'

Regards
Sreeni

[ray.wurlod]

Maybe 365.249 as the divisor?

[DWH-M]

Thanks buddies for your valuable inputs.

I understand MONTHS_BETWEEN database function will work.

Can any one suggest if there is any datastage function similar to the above one? or any combination of functions available in datastage which can be used in transformer?

Cheers.

[ArndW]

Use MonthFromDate() to get the month from a date, YearFromDate() to get the year from a date and then a bit of simple subtraction and logic to get the delta months between two dates.

[Ravi.K]

There is no builtin Datastage functions available for months_between as Database functions.

I managed to get the functionality of Months_between in datastage using the following devirations. but it is getting fail at date part.

DateA=2000-11-15 DateB=2010-06-15

So, i consider only years and months to get the differences.

Here DateB should always greater that DateA.

YearA =YearFromDate(DateA)
YearB=YearFromDate(DateB)
MonthA=MonthFromDate(DateA)
MonthB=MonthFromDate(DateB)

if Month B > MonthA Then

((YearB - YearA)*12)+(MonthB - MonthA)

Else

((YearB - YearA)*12)+(12 + MonthB - MonthA)-12

If any one find how to handle dates, Please add the functionality.

[chulett]

You might want to explain what "getting fail at date part" means.

[Ravi.K]

Sorry typo error. Facing little problem at handling "Date Part".

The logic revised little bit.

DateA=2000-11-15 DateB=2010-06-12

YearA =YearFromDate(DateA)
YearB=YearFromDate(DateB)
MonthA=MonthFromDate(DateA)
MonthB=MonthFromDate(DateB)

((YearB - YearA)*12)+(MonthB - MonthA)

Add your thoughts to handle "Date Part" to yield months_between functionality.

[chulett]

I wasn't trying to point out a typo, something I don't see btw, I was trying to point out the fact that you mentioned some kind of "failure" without explaining at all what the nature of your failure was.