Best Way to Calculate Age

Question

Post reply

Best Way to Calculate Age

Viewing 15 posts - 46 through 60 (of 189 total)

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

JLSSCH Ten Centuries Points: 1013 More actions · Answer 1

I agree with SSC Eights for the third solution!!!!!

Here it is:

SELECT 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 1

ELSE 0 END

Victor Abkin SSC-Addicted Points: 426 More actions · Answer 2

The third way is wrong for my birthday, which is 08/30/1954.

As of today I'm 53 years old, but it returned 54.

So definitely second way of age calculation is the best!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I want my point back too....

Here is the code as run with results:

declare @DateOfBirth datetime

set @DateOfBirth = '1959-08-29 09:59:00.000'

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

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

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

set @DateOfBirth = '1959-01-29 09:59:00.000'

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

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

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

@DateOfBirth = '1959-08-29 09:59:00.000'

---------------------------------------

49

48

49

(3 row(s) affected)

@DateOfBirth = '1959-01-29 09:59:00.000'

---------------------------------------

49

48

(3 row(s) affected)

😎

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 4

It should've been:

SELECT DATEDIFF(yy, 'DOB', GETDATE()) -

CASE WHEN DATEPART(m, 'DOB') >= DATEPART(m, GETDATE())

AND DATEPART(d, 'DOB') > DATEPART(d, GETDATE())

THEN 1 WHEN DATEPART(m, 'DOB') >= DATEPART(m, GETDATE())

AND DATEPART(d, 'DOB') >= DATEPART(d, GETDATE()) THEN 0 ELSE 0 END

All of it was wrong.. the case, the >= signs.. everything

Mike B in AK SSC Eights! Points: 900 More actions · Answer 5

The "best way" was not the accurate way. I ran all three of the options and my own age calculation function. I checked the accuracy of all three and only option 2 was closest, but it was not accurate all of the time. Once I switched the 365.0 to 365.25, option 2 did return the correct answers.

Perry Harts Ten Centuries Points: 1271 More actions · Answer 6

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

is incorrect, it 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

gwyn.williams SSC Veteran Points: 282 More actions · Answer 7

The third query has the 1 and 0 the wrong way round in the ELSE THEN part. my DOB is 9/6/66 and I'm not 42!! yet!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Here is some code that works:

declare @DateOfBirth datetime

set @DateOfBirth = '1959-08-29 09:59:00.000'

select datediff(yy, @DateOfBirth, getdate()) - case when dateadd(yy,datediff(yy, @DateOfBirth, getdate()), @DateOfBirth) > getdate() then 1 else 0 end

set @DateOfBirth = '1959-01-29 09:59:00.000'

select datediff(yy, @DateOfBirth, getdate()) - case when dateadd(yy,datediff(yy, @DateOfBirth, getdate()), @DateOfBirth) > getdate() then 1 else 0 end

😎

Evgeny-593654 SSC Enthusiast Points: 157 More actions · Answer 9

I don't agree with question options. For my DOB I get right answer with #3. But it is not the best way.

I can define age as:

DATEDIFF(yy, 0, DATEDIFF(dd, DateOfBirth, GETDATE()))

On simple question should be simple answer.

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 10

All of the answers are flawed

Answer 1 (intentionally wrong): only calculates the value in difference between the 2 years (As BOL states)

Answer 2 (intentionally wrong): doesn't take into account leap years.

Answer 3 (supposedly correct): tries to subtract 1 from the original calculation given in Answer 1, but fails as it checks to see if the month value AND the day value are greater than or equal to the current day, however when you run this today(6 March) for a date of birth of 1 Apr, then 1>=6 AND 4(Apr) >=3(Mar) = FALSE.

what should be checked is a combination of month and day, i.e. (4>3) OR (4=3 AND 1>6)

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 1

ELSE 0

END

also note that the 1 and 0 are transposed.

Sudarshan Alwar A SSC Veteran Points: 223 More actions · Answer 11

The same here. The second query provides the exact age rather the third provides the age based on the year. If DOB is 15-04-1986 & the present date is 06-03-2008, the age will be almost 22 but not 22. The age will still be 21 & the query-2 gives the result expected & not the 1st & 3rd.

Please let me know I am incorrect.

Thanks

Sudarshan

Cheers!
Sudarshan

Amit Tiwari-332074 SSC Veteran Points: 236 More actions · Answer 12

I think Case condition has to be reversed and then we get the right answer

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

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

I did not answer today's question since none of the answers is correct.

First, none of the answers returns a tinyint. One returns decimal(9,2), the others return int

Second, they all produce incorrect results, as can be see by the results of this script, which sets to March 7, 2007 (so I'll be celebrating my first birthday tomorrow - and still 0 years of age today)

DECLARE @DateOfBirth datetime;

DECLARE @getdate-2 datetime;

SET @DateOfBirth = '20070307';

SELECT GETDATE();

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;

Results:

-----------------------

2008-03-06 11:49:48.500

-----------

1

---------------------------------------

1

-----------

1

Let me know when a fourth possiblility (either "none of the above" or a correct query) has been added. Until then, I'll have to pass on this QotD.

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/

Squig SSC-Addicted Points: 433 More actions · Answer 14

Agree with the fourth statement, it's the only one that returns a correct value.

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 15

/*

I was born on Nov/16/1957 therefore I am 50 years of age. However, the only correct statement, regardless the returning data-type, is the second one. So, I am not going to discuss any other aspect of this question of the day!!

*/

declare @DateOfBirth datetime

select @dateofbirth = '11/16/1957'

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

-- thx

Cheers,
John Esraelo