There are a million blog posts on this sort of thing but it just bit us so I thought I’d add mine to the mix.
Date calculations can be notoriously difficult depending on the language you are using. However, DO NOT succumb to the desire to "fake" it as our vendor did. Here’s what happened:
In California every school has to submit data for their special ed students/programs to receive proper funding. This is called a CASEMIS report, and reporting day is every June 30th and December 1st.
For CASEMIS if a child is under 6 they are in one category and if 6 and over in another.
In our data there is one student in question who’s date of birth is 12/3/2002. So on the reporting date of 12/1/2008 this student will be just two days shy of 6 years old.
We were getting an error in our validation process because apparently our vendor was calculating ages using 1 year = 365 days. 12/1/2008 – 12/3/2002 yields 2190 days, which at 365 days a year results in 6 years exactly.
Ugh! Sorry, but the "close enough for government work" doesn’t apply here.
I don’t know exactly what language they are using but I believe they use perl on the back end and Business Objects InfoView XI for the reporting. The actual date calculation could be in either of these two places, or somewhere entirely different.
While some languages make calculating proper dates difficult, there is almost always a correct way to do it.
For instance, in Microsoft T-SQL, asking the difference in years between 12/1/2008 and 12/3/2002 using:
SELECT DateDiff(yy, ’12/3/2002′, ’12/1/2008′)
incorrectly returns 6 years, since 2008 – 2002 = 6.
However, if you change the question slightly and ask if the birthdate + 6 years is less than the reporting date using:
SELECT CASE WHEN DateAdd(yy, 6, ’12/3/2002′) < ’12/1/2008′ THEN ‘6 or Over’ ELSE ‘Under 6’ END
this results in the correct response “Under 6”.
It’s a matter of finding the proper question and syntax for the language.