Find Number of years between 2 dates.
Moderators: chulett, rschirm, roy
Find Number of years between 2 dates.
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.
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.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Code: Select all
1900-03-20 (yyyy-mm-dd) to 2010-11-20 (yyyy-mm-dd)
Wondering it should be something 110 years 8 months?
Are you expecting 110.8 as output?
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Cheers
Ravi K
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.
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.
Cheers
Ravi K
Ravi K