# Best Way to Calculate Age

• There are many ways to give the right answer...

Your solution is returning me a star character (*)... A dimension musl be provided to the @result variable declaration: Declare @result VarChar(10)... But don't use a local variable unless it is required...

I really prefer an Integer output... Here is another solution...

Declare @DateOfBirth DateTime

Set @DateOfBirth = '1983-07-10'

Select (DateDiff(Year, @DateOfBirth, GetDate()) -

Case When Convert(SmallDateTime, Convert(Char(4), DatePart(Year, GetDate()))

+ SubString(Convert(Char(10), @DateOfBirth, 121), 5, 6))

<= GetDate() Then 0 Else 1 End)

• It looks like some queries work for some people and other queries work for some other people: the only thing I know is I would never put any of these in my code...:D

-- Gianluca Sartori

• Gianluca,

You wrote:

It looks like some queries work for some people and other queries work for some other people: the only thing I know is I would never put any of these in my code...

What would you not use? Why not? For optional extra credit, what would you use?

• Siendo hoy 17-07-2008

declare @DateOfBirth datetime

set @DateOfBirth = '19660117'

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

años

-----------

41

entonces me equivoque al festejar mis 42 años !!!!!!!:w00t:

• pjaime (7/17/2008)

Siendo hoy 17-07-2008

declare @DateOfBirth datetime

set @DateOfBirth = '19660117'

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

años

-----------

41

entonces me equivoque al festejar mis 42 años !!!!!!!:w00t:

PJaime,

Logical "AND" is not the same as concatenating first and then comparing. Look at your comparison with values substituted for the functions:

DOBMonth=01

DOBDay=17

TodayMonth=07

TodayMonth= 16

`If 01 >= 07 and 17 >= 16 then 0 Else 1 -- FALSE. 1 is NOT greater than 7, so you subtract 1 from años`

You might use an OR to build code that results in this evaluatioin with the values given above:

`If 01 < 07 OR (01 = 07 AND 17 <= 16)`

or you can concatenate the month and day parts before doing a single comparison

`If 0117 <= 0716 `

• None of the three options given are completely correct. For the same month, if the DOB has not yet arrived, all the three options give the wrong age.

• Specified Reasons are quite enough to understand that why below method is more accurate.

But actually author made a mistake here.

I mean that Method is right but query written is logically wrong.

Wrong one specified in problem:

select DATEDIFF(yy, DateOfBirth, GETDATE()) -

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

Corrected one:

select DATEDIFF(yy, DateOfBirth, GETDATE()) -

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

:P:)

• Jesse McLain (3/6/2008)

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

The missing #4 is not the correct answer, because each year it is 1 year out on the birthday (it's right all other days of the year).

Tom

• I was robbed....:( the 3rd answer is wrong!

• Third option calucates wrong age

Viewing 10 posts - 181 through 189 (of 189 total)