# Best Way to Calculate Age

• I also ran all three queries with my birthdate, and the first and third returned the wrong answer. However, the second one was correct.

• That one works flawlessly.

DECLARE @DateOfBirth VARCHAR(10)

SET @DateOfBirth = '1960-03-07'

print YEAR(GETDATE()) - YEAR(@DateOfBirth) - CASE WHEN MONTH(GETDATE())*31 + DAY(GETDATE()) >= MONTH(@DateOfBirth)*31 + DAY(@DateOfBirth) THEN 0 ELSE 1 END

• The formula is incorrect. If your birthday has occurred in the calendar year, the formula below returns 1 year less than it should. I know many people that would be happy if the formula was correct, but I am afraid they really are the age that DATEDIFF(yy, DateOfBirth, GETDATE()) gives.

DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

Q

Please take a number. Now serving emergency 1,203,894

• Another point - the question specifies TINYINT, none of the queries return TINYINT. (1) is INT, (2) is DECIMAL, (3) is INT. Not only are the queries inaccurate, but none of them fit the criteria ... :crazy:

• sorry the 3rd answer is not 100% correct as written

DECLARE @DateOfBirth DATETIME

SET @DateOfBirth='08/xx/1975' --commented out the day just because

SELECT DATEDIFF(yy, @DateOfBirth, GETDATE()) AS [first],

FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0) AS [second],

DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END AS [third]

the result when it runs

firstsecondthird

333233

Don't age me before my time, only the second one was correct.

• Sorry everyone, I really got egg on my face with this one. :ermm: When I posted the question, I copied the wrong answer for answer #3. The missing #4 is the only correct answer:

DECLARE @BirthDate datetime

SET @BirthDate = '3/7/1908'

SELECT

--#1:

DATEDIFF(yy, @BirthDate, GETDATE()),

--#2:

FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @BirthDate, GETDATE())) / 365.0),

-- #3:

DATEDIFF(yy, @BirthDate, GETDATE()) - CASE WHEN DATEPART(m, @BirthDate) >= DATEPART(m, GETDATE()) AND DATEPART(d, @BirthDate) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END,

-- the missing #4:

DATEDIFF(yy, @BirthDate, GETDATE()) - CASE WHEN DATEPART(m, @BirthDate) < DATEPART(m, GETDATE())

OR (DATEPART(m, @BirthDate) = DATEPART(m, GETDATE()) AND DATEPART(d, @BirthDate) < DATEPART(d, GETDATE())) THEN 0 ELSE 1 END

Assuming that GETDATE() = '3/6/2008', answers 1-3 will return 100 as the age, whereas #4 will return 99, the correct age.

Again, really sorry for the mixup.

Cheers,

Jesse

• Interesting fact to know about DATEDIFF -- seems like the wrong way to code it to me, but oh well . . .

Actually, none of the answers are right. Try:

DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) > DATEPART(m, GETDATE()) OR (DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE())) THEN 0 ELSE 1 END

Pretty ugly. Why isn't there a Julian DATEPART?

• The logic in the third statement does not properly relate the day of the month to the month of the year. It should read:

select DATEDIFF(yy, BirthDate, GETDATE())

- CASE

WHEN DATEPART(m, BirthDate) >= DATEPART(m, GETDATE()) THEN 1

WHEN DATEPART(m, BirthDate) = DATEPART(m, GETDATE())

AND DATEPART(d, BirthDate) >= DATEPART(d, GETDATE())

THEN 1

ELSE 0

END

• Aye, your fourth statement is what works correctly, Peter.

• I ran all three queries against my own birth date. The first and the third ones returned the wrong value. The second one returned the correct result.

Please fix this and re-score everyone who turned in the correct answer.

By the way, dosen't anyone there check the questions for accuracy before you put them out?

-- WP

• The second query would give a reasonable answer if you use 365.25 instead of 365.

The third query will not work if DOB is, for example, Feb 7 1987 and the month is March (now) but the day is 6 (today). You need *nested* loops -- check for month, then check for day only if the month is equal. (Once current month is greater than birth month, you do not need to check the day.)

I know a lot about this because I created the same problem for my S.Q.L. students! Plus I had someone's GETAGE user-defined function that worked as I described.

• The third query is wrong:

DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) > DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) > DATEPART(d, GETDATE()) THEN 1 ELSE 0 END

Note the requirement that 1 should be subtracted from the following if the person's birthday has not yet been attained as of GETDATE(). Also, to berify the person's birthday has not yet been attained, we need to use the ">" sign instead of the ">=" sign.

• declare @DoB datetime

declare @Today datetime

declare @YearsOld tinyint

set @DoB = '3/5/1985 6pm'

-- Tests.

set @Today = '3/5/2008 2am'

set @Today = '3/5/2008 5:59pm'

set @Today = '3/5/2008 6:00pm'

--set @Today = '3/5/2008 6:01pm'

--set @Today = '3/5/2008 7pm'

--set @Today = getdate()

;WITH-- Jeff Moden's CTE replacement for number table.

cTally AS(

SELECT TOP (2400) -- 200 years of months.

ROW_NUMBER() OVER (ORDER BY t1.ID) AS Number

FROM master.sys.SysColumns t1

CROSS JOIN master.sys.SysColumns t2

)

select

@YearsOld = count( * ) / 12

from cTally

where DateAdd( month, Number, @DoB ) <= @Today

select @YearsOld

• I have to agree the third statement just produces the wrong value. The 2nd is closest although it should divide by 365.25 rather than 365 but the difference between your real age and the calculated one is negligible enough not to produce an error (in terms of years) even if you are several hundreds of years old. The second while not being strictly exact is the only one that produces the correct age.

Francis

• That's a bug in sqlserver.

Datediff(m,@birthdate,getdate()) subtracts dates and returns months.

Datediff(d,@birthdate,getdate()) subtracts dates and returns days.

Datediff(yy,@birthdate,getdate()) subtracts year parts, contrary to expectations and documentation wich says "Returns the number of intervals between two dates" - so it should return number of years between dates.

BOL also says:

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

From this you can deduce it subtracts year parts and not calculate year part of date difference.