Hi Scott & GSquared,

My common sense was actually not about when a birthdate is (I have at times celebrated by birthday several days before or after the day I was...

GSquared (3/10/2008)

---

Lynn and Hugo:

I took Leap Years into account, and even (to be totally anal retentive about it), the hour, minute, second, and number of milliseconds, on each time.

Maybe I'm...

GSquared (3/10/2008)

---

Here's the function I recommend for this:

select datediff(yy, @dateofbirth, getdate()) -

case

when dateadd(year,

datediff(yy, @dateofbirth, getdate()),

@dateofbirth) > getdate() then 1

else 0

end

This takes the datediff, adds it back to the...

Lynn Pettis (3/10/2008)

---

Here is a formula that appears to work okay in SQL Server:

datediff(yy, @dob, @calcDate) - case when (datepart(dy, @calcDate) < datepart(dy, @dob)) then 1...

Jamie Longstreet (3/10/2008)

---

I see Hugo's point. You cannot take into account leap years and other such anomalies when you are creating a calculation using months. Months are unreliable...

Rick Harker (3/10/2008)

---

1) I see the same result. I used to go to school with a woman who claimed she was only 4 because of her leap year birthday. :)...

Jamie Longstreet (3/10/2008)

---

Hugo,

Not sure what you mean but I took the date you implied (37 years and 9 months ago) and ran the routine and came up with 37 and...

Sorry, Rick - I...

Jamie Longstreet (3/10/2008)

---

Hugo,

http://www.sizes.com/time/cal_gregorian.htm

Try changing the Int to Round. Note that the calculation takes into account the factors you mentioned. I just put this one out there...

Hi Jamie,

Interesting approach - but unfortunately, it shares the problem also exhibited by several other suggestions posted here, that it won't work correctly in non-leap years.

For instance, the calculated difference...

If you look back through the 12 pages of discussion, you'll see several...

Robert Price (3/8/2008)

---

Reverse the values of 0 and 1 for the condition and else in the case statement and #3 is correct...

I'm sure I wasn't the first to notice this...

louie.cowen (3/7/2008)

---

Most people seem to be modifying the 3rd option to make it correct, but the best answer is what is the simplest, meets ALL the criteria (results in tinyint)...

Nope. This formula says that someone born on March 5 2003 is 1 year...

Trader Sam (3/7/2008)

---

Hugo...