Best Way to Calculate Age

Question

Post reply

Best Way to Calculate Age

Jesse McLain

SSCommitted

Points: 1782
More actions
March 6, 2008 at 3:40 am

#179797

Comments posted to this topic are about the item Best Way to Calculate Age
Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com

Viewing 15 posts - 1 through 15 (of 189 total)

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

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

If a person's DOB = '1988-02-24 15:00:00.000', the age should be 20 already as of today (March 5th, 2008).

But the 3rd query returns 19.

select DATEDIFF(yy, '1988-02-24 15:00:00.000', GETDATE()) - CASE WHEN DATEPART(m, '1988-02-24 15:00:00.000') >= DATEPART(m, GETDATE()) AND DATEPART(d, '1988-02-24 15:00:00.000') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

Does the 3rd query return the right age?

David in .AU SSCommitted Points: 1784 More actions · Answer 2

I ran all 3 queries with my DOB and the first and 3rd ones returned the incorrect value.

The 2nd query had the correct value.

looking at the case statement I think you have the 1 and 0 around the wrong way

Marko Frkovic Ten Centuries Points: 1096 More actions · Answer 3

I also ran all 3 queries for my DOB and 2nd was correct, both 1st and 3rd were wrong.

Jeff Deluca Ten Centuries Points: 1152 More actions · Answer 4

The first and second answers were correct for me. The third answer was definitely wrong in my case. :hehe:

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 5

Jumping on the bandwagon here: only the second statement returned the correct value for my birthdate. Gimme my point! ARGH!

😛

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

scottm30 Ten Centuries Points: 1094 More actions · Answer 6

Howdy, thanks for the interesting question.

The 3rd answer was wrong for my DOB. It showed a younger value though, which is appreciated.

Peter Feeney SSC Enthusiast Points: 162 More actions · Answer 7

Peter Feeney

SSC Enthusiast

Points: 162

March 6, 2008 at 6:43 am

#785915

Definitely the second answer!

Anthony K. Valley SSC-Addicted Points: 471 More actions · Answer 8

In my case, like many others, the second answer was the only correct one. My birthday is not until September.

mark.hammond SSC Veteran Points: 267 More actions · Answer 9

The third solution returns "51" for DOB 08/21/1957.

select DATEDIFF(yy, '08/21/1957', GETDATE()) - CASE WHEN DATEPART(mm, '08/21/1957') >= DATEPART(mm, GETDATE()) AND DATEPART(d, '08/21/1957') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

Clearly, this is INCORRECT.

Jimi Meyer SSCommitted Points: 1764 More actions · Answer 10

Only the second query is getting the right answer. #1 and #3 are getting +1 to the my current age.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 11

The third solution is clearly incorrect - given a datetime value of 01/26/1978.

i.e.

declare @DateOfBirth datetime

set @DateOfBirth = '1978-01-26 00:00:00.000'

select @DateOfBirth

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

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

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

Returns:

30 - Correct

29 - Incorrect

Tommy

Follow @sqlscribe

matzer SSCrazy Eights Points: 9727 More actions · Answer 12

I intuitively choose 2 because 1 and 3 are definitely wrong. 1 doesn't take care of month and day of birth at all and 3 does it in the wrong way. But the are all wrong. It can be easily verified with the script below. Play around with some values at the beginning and end of the month in both dates. I added a fourth solution which I think is correct.

declare @DateOfBirth datetime

declare @Today datetime

select @DateOfBirth = '2004/03/01'

select @Today = '2008/02/29'

select

DATEDIFF(yy, @DateOfBirth, @today)

select

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

select

DATEDIFF(yy, @DateOfBirth, @today)

- CASE

WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, @Today) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, @Today) THEN

0

ELSE

1

END

select

DATEDIFF(yy, @DateOfBirth, @today)

- CASE

WHEN DATEPART(m, @Today) > DATEPART(m, @DateOfBirth)

OR (DATEPART(m, @Today) = DATEPART(m, @DateOfBirth)

AND DATEPART(d, @Today) >= DATEPART(d, @DateOfBirth)) THEN

0

ELSE

1

END

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 13

Of course, I suppose the real point of the question was to draw attention to the fact that (1) was wrong and (2) was inaccurate. But it would have been helpful to have a correct solution in the mix. 🙂

Derek

Steven Cameron SSCrazy Points: 2903 More actions · Answer 14

I originally thought the 1 and 0 were switched but actually, the two comparisons should be >=