Best Way to Calculate Age

Question

Best Way to Calculate Age

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

You must be logged in to reply to this topic. Login to reply

Luc Philie SSC Rookie Points: 32 More actions · Answer 1

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)

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

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

john.arnott SSChampion Points: 11882 More actions · Answer 3

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?

pjaime SSC Rookie Points: 40 More actions · Answer 4

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

el resultado es:

años

-----------

41

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

john.arnott SSChampion Points: 11882 More actions · Answer 5

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
el resultado es:
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

Your original code evalutes to:

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

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 6

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.

gyanp.garg SSC Veteran Points: 274 More actions · Answer 7

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:)

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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