Best Way to Calculate Age

Question

Post reply

Best Way to Calculate Age

Viewing 15 posts - 166 through 180 (of 189 total)

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

Hank Cao Newbie Points: 3 More actions · Answer 1

The following formula could be more accurate

(DATEDIFF(yy, @dob, getdate()) - CASE

WHEN DATEPART(m, @dob) < DATEPART(m, getdate()) THEN 0

WHEN DATEPART(m, @dob) = DATEPART(m, getdate()) AND DATEPART(d, @dob) <= DATEPART(d, getdate()) THEN 0

ELSE 1 END) end

Hank Cao

Brian Ferguson SSCommitted Points: 1837 More actions · Answer 2

Using my DOB, None of the 3 technically gave the correct answer.

WHen I ran it my DOB was 4 days in the future

Setting my DOB to 2 months, 4 days in the future, this statement was the ONLY one that was correct:

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

arcain SSC Journeyman Points: 99 More actions · Answer 3

I eliminated numbers 1 and 3 in my head since it didn't calculate my age correctly, and picked 2. Oh well.

Anyway, it looks like number 3 should be:

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

(changed the >= on the day comparison to > since most people count their birthday as the day they get older, and not the day after.)

tim.mulherin SSC Rookie Points: 43 More actions · Answer 4

Indeed, the 0 and the 1 should be reversed. However, if you plug in a birthdate that occurs on the current Month/Day, it is incorrect.

rui.romao SSC Rookie Points: 27 More actions · Answer 5

Hello,

The "AND" in de case is clearly wrong because if the day of the BirthDay is inferior to the one obtain in the GETDATE() it will always return 0.

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

Nicole Bowman SSCommitted Points: 1555 More actions · Answer 6

I agree with others here. Using my birth date the only one that gave me the correct answer was the second one and my birthday is 7 months away.

I want my point! 🙂

Nicole Bowman

Nothing is forever.

ephraims Valued Member Points: 74 More actions · Answer 7

The second answer is the most accurate of the three; however, it does not account for leap years. The third answer would be the correct answer if the 1 and 0 were switched around.

aarif.pd Grasshopper Points: 13 More actions · Answer 8

aarif.pd

Grasshopper

Points: 13

April 10, 2008 at 4:30 am

#800863

the second one is correct. give me my point.

Protiguous SSC Eights! Points: 915 More actions · Answer 9

aarif.pd (4/10/2008)
the second one is correct. give me my point.

No, it is not. 🙂

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

Goodness, this one's gone on, hasn't it? And now I'm in it cuz of the row-size QOTD that had a naive age calculation thrown in.

Anyway, before reading through all 19 pages, I coded this:

Declare @DOB smallDatetime

Declare @Today smallDatetime

set @DoB = '02/29/2004'

set @Today = '2/29/08'

select @dob as Dob

,@today as Today

,year(@today)-year(@dob)

- casewhen month(@today) < month(@dob)

or (month(@today) = month(@dob) and day(@today) < day(@dob))

then 1

else 0

end

as Age

It turns out that it's pretty similar to some other solutions agreed to be correct, except that I used the functions "year()", "month()", and "day()" rather than "Datepart()".

Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

john.arnott (4/10/2008)
Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.

Hi John,

YEAR(...) is synonym for DATEPART(year, ...); similar for DAY and MONTH. From a technical point of view, there's no preference.

The only thing left is readability. Some, like you, prefer the shorter versions because they are shorter and hence easier to understand. Others prefer DATEPART because you can also use that to expose dateparts that have no synonym (like, for instance, week, minute, quarter) and they consider it more consistent to use the same function in all cases.

All in all, just a matter of personal preference. Though I'd recommend you to follow shop standards if there are any.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Robert Knights SSC Rookie Points: 37 More actions · Answer 12

Love all the trick questions in the QOD stuff - I never even looked past the first one as even though it may not always give the correct birthdate in English/American terms, it could be adjusted to more readably do so by flooring the other values out of the year! Hand up, I got it wrong, but then IMO not a great question since the actual "correct" answer doesn't work either.

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

Declare @d smalldatetime

set @d = '1957/7/5'

Select DATEDIFF(yy, @d, GETDATE()) -

CASE WHEN

DATEPART(m, @d) < DATEPART(m, GETDATE())

OR (

DATEPART(m, @d) = DATEPART(m, GETDATE())

AND

DATEPART(d, @d) <= DATEPART(d, GETDATE())

) THEN 0 ELSE 1 END

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 14

Luc Philie (7/3/2008)
Declare @d smalldatetime
set @d = '1957/7/5'
Select DATEDIFF(yy, @d, GETDATE()) -
CASE WHEN
DATEPART(m, @d) < DATEPART(m, GETDATE())
OR (
DATEPART(m, @d) = DATEPART(m, GETDATE())
AND
DATEPART(d, @d) <= DATEPART(d, GETDATE())
) THEN 0 ELSE 1 END

And your point is...?

Derek

Mayuresh Mr or Mrs. 500 Points: 520 More actions · Answer 15

declare @DateOfBirth datetime

declare @result as varchar

set @DateOfBirth='1983-07-11'

select @result = (DATEDIFF(yy, @DateOfBirth, GETDATE()) -

case when Convert(Datetime,Convert(varchar,DATEPART(yy, GETDATE())) + '-' + Convert(varchar,DATEPART(m, @DateOfBirth))+ '-'+ Convert(varchar,DATEPART(d, @DateOfBirth)))

<=GetDate() THEN 0 ELSE 1 END )

select @Result

Here is the correct query to retrieve exact age.

😎

MayurArt