Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1516171819

Best Way to Calculate Age Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 8:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, July 16, 2008 6:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 4,609, Visits: 11,010
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #535143
Posted Wednesday, July 16, 2008 9:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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
Posted Thursday, July 17, 2008 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 !!!!!!!
Post #535951
Posted Thursday, July 17, 2008 9:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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 !!!!!!!

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
Posted Tuesday, November 4, 2008 2:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:18 AM
Points: 556, Visits: 277
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
Posted Sunday, March 8, 2009 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, April 22, 2010 7:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:47 AM
Points: 7,855, Visits: 9,603
Jesse McLain (3/6/2008)
Sorry everyone, I really got egg on my face with this one. 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
Post #909113
Posted Wednesday, June 1, 2011 12:11 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 2012 4:02 AM
Points: 660, Visits: 134
I was robbed.... the 3rd answer is wrong!
Post #1118267
Posted Wednesday, November 9, 2011 2:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:57 AM
Points: 278, Visits: 142
Third option calucates wrong age
Post #1202714
« Prev Topic | Next Topic »

Add to briefcase «««1516171819

Permissions Expand / Collapse