Simple age calculation 2

[Cheveliar]

I have a birth date and another date that I want to use to calculate the age of a person during a period other than what is represented if I used getdate(). Problem is using datediff gives me the years but skips the calculation of the actual date of birth during the other period. Example: I have a birth date of 2/13/65 my diff date(s) are 1/15/04 and 2/20/04. Both records give me a year number of 39, however on 1/15/04 the person was 38 and on 2/20/04 the person aged to 39. Is there a way to refelct that in my calculation?

 

[SQLBill]

You are having problems because you are only comparing the YEARS. Try comparing the months and dividing the result by 12....


SELECT DATEDIFF(MONTH, '2/13/65', '1/15/04')/12
SELECT DATEDIFF(MONTH, '2/13/65', '2/20/04')/12

-SQLBill

 

[Cheveliar]

Thanks Bill that was right on time...

 

[cjlarue]

Can we take this a step further? I need to display age in months and have a similar problem in that datediff gives me the same value (36 for example) whether the birthdate is
1/1/2000 or 1/28/2000 from 1/1/2003. the first is 36 months old but the second is not.

Regards
Chuck LaRue
ADRS Computer Services

 

[BobJacksonNCI]

Try thread183-672568


HTH,
Bob

 

[TheBugSlayer]

By analogy find the difference in days and divide by...30, 31? You might have to use a little trick here...Leap years are in the middle too...

 

[cjlarue]

Ok Bugs...,Bob... the anology is clear but my client doesn't care about the little quirks. they want to know how many referrals by age in months. They cannot serve clients after 36 months old and freak out when these show up on the reports. days would get it a little closer but could still leave me off on a couple of cases which is enough for them to keep calling me. Thanks for the suggestions.

 

[BobJacksonNCI]

DateDiff(month,dtField,GetDate()) < '36' OR (DateDiff(month,dtField,GetDate()) = '36' AND DatePart(day,dtField) < DatePart(day,GetDate()))

Looks right, but not tested.





HTH,
Bob

 

[cjlarue]

Bob.. You're the man
This is what I ended up with

Months_At_Referral = Case when (Datediff(Month, P.Birth_Date, CM.Application_Date)=36 and (Datepart(d, P.Birth_Date)> Datepart(d,CM.Application_Date))) then '35'
ELSE
Datediff(Month, P.Birth_Date, CM.Application_Date)
END,

 

[BobJacksonNCI]

Chuck,

Thanks for the star!

You did a lot of the work, I just guided a bit.

Happy Computing!
Bob