Hi James
Very interesting - many similar experiences. I wrote some software for an insurance company to illustrate a "whole of life" insurance product. I hand checked that the results of my calculations matched the supplied formulae. When I submitted my software I was informed that the calculations were incorrect because they didn't agree with the mainframe program. I admitted that they could be wrong but so could the mainframe. (In extreme cases my result was nearly twice theirs). So I asked their staistician who was responsible for the product to hand check a few examples with me. He said that that would be too difficult (I didn't agree but wasn't going to be able to convince him) so he wrote his own little program in APL to check the results. Well his results didn't agree with mine or the mainframe. I helped him debug his program and guess what - his results and mine agreed. Problem solved you might think. No! I was told that it would take at least 2 years to get the mainframe program fixed (why?). Could I modify my code to give the same (incorrect) result as the mainframe? which is what I had to do. Like you I had to get them to run some sample calculations for me on the mainframe and then figure out what it was doing.
Re calculation of age, I believe this is the simplest way (following code written as I go so not checked but I know the method is reliable even if I make a simple mistake here.)
- Code: Select all Expand view
FUNCTION AgeInYears( dFromDate, dToDate )
LOCAL nYearDiff
nYearDiff := Year( dToDate ) - Year(dFromDate )
IF Month( dToDate ) > Month( dFromDate )
RETURN nYearDiff
ENDIF
IF Month( dToDate ) < Month( dFromDate )
RETURN nYearDiff - 1
ENDIF
// if we reach here we are in the month of birth date
IF Day( dToDate ) >= Day( dFromDate )
RETURN nYearDiff
ELSE
RETURN nYearDiff - 1
ENDIF
Not affected by leap year issues and fairly simple. Your age is simply the difference between the two years provided you are past the date of birth in the current year, else it is 1 less.
Limitations:
1. Assumes that dFromDate and dToDate are passed in the correct order. Fix is easy if required but depends on what you want to happen if the order is reversed.
2. Would have a problem if either or both dates are BC
3. You may want different treatment for ages below say 1 year old. For example change function to return a string with 1-6 days, 1- 52 weeks, 1+ years.
4. You may want a fractional age or an age in years and months.
5. A way to minimise date reversal issue in some circumstances would be to have an AgeAsAtToday( dDOB ) function, which given the above could be
- Code: Select all Expand view
FUNCTION AgeAsAtToday( dDOB )
RETURN AgeInYears( dDOB, DATE() )
I would commend the above basic approach to you.