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.
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- 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:
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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