Best Way to Calculate Age

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Best Way to Calculate Age

190 posts, Page 19 of 19

«««15 16 17 18 19

Best Way to Calculate Age

Rate Topic

Display Mode

Topic Options

Author

Message

Luc Philie

Posted Thursday, July 10, 2008 8:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, March 18, 2012 1:38 AM
Points: 26, Visits: 20

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)

Post #531684

spaghettidba

Posted Wednesday, July 16, 2008 6:39 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 4,804, Visits: 8,068

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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #535143

john.arnott

Posted Wednesday, July 16, 2008 9:23 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

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?

Post #535333

pjaime

Posted Thursday, July 17, 2008 7:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 14, 2011 11:20 AM
Points: 36, Visits: 12

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

Post #535951

john.arnott

Posted Thursday, July 17, 2008 9:44 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

pjaime (7/17/2008)

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 &lt; 07 OR (01 = 07 AND 17 &lt;= 16)

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

If 0117 &lt;= 0716

Post #536122

Sanjay Rohra

Posted Tuesday, November 04, 2008 2:48 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 25, 2013 1:40 AM
Points: 498, Visits: 262

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.

Post #596397

gyanp.garg

Posted Sunday, March 08, 2009 7:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 4:03 AM
Points: 185, Visits: 52

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

Post #671100

L' Eomot Inversé

Posted Thursday, April 22, 2010 7:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 7,096, Visits: 7,156

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #909113