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 ««12345»»»

Best Way to Calculate Age Expand / Collapse
Author
Message
Posted Thursday, March 6, 2008 7:04 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 3:03 PM
Points: 1,473, Visits: 574
Only the second query is getting the right answer. #1 and #3 are getting +1 to the my current age.
Post #465123
Posted Thursday, March 6, 2008 7:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,157, Visits: 3,268
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

30 - Correct

29 - Incorrect


Tommy

Post #465125
Posted Thursday, March 6, 2008 7:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:46 AM
Points: 1,598, Visits: 6,646
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

Post #464921
Posted Thursday, March 6, 2008 7:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:17 AM
Points: 1,274, Visits: 1,985
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
Post #465138
Posted Thursday, March 6, 2008 7:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 6, 2009 1:29 PM
Points: 2,057, Visits: 215
I originally thought the 1 and 0 were switched but actually, the two comparisons should be >=
Post #465141
Posted Thursday, March 6, 2008 7:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:39 AM
Points: 2,924, Visits: 735
I also ran all three queries with my birthdate, and the first and third returned the wrong answer. However, the second one was correct.
Post #465142
Posted Thursday, March 6, 2008 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:20 AM
Points: 96, Visits: 44
That one works flawlessly.

DECLARE @DateOfBirth VARCHAR(10)
SET @DateOfBirth = '1960-03-07'

print YEAR(GETDATE()) - YEAR(@DateOfBirth) - CASE WHEN MONTH(GETDATE())*31 + DAY(GETDATE()) >= MONTH(@DateOfBirth)*31 + DAY(@DateOfBirth) THEN 0 ELSE 1 END

Post #465147
Posted Thursday, March 6, 2008 7:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, Visits: 165
The formula is incorrect. If your birthday has occurred in the calendar year, the formula below returns 1 year less than it should. I know many people that would be happy if the formula was correct, but I am afraid they really are the age that DATEDIFF(yy, DateOfBirth, GETDATE()) gives.

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


Q

Please take a number. Now serving emergency 1,203,894

Post #465150
Posted Thursday, March 6, 2008 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:06 AM
Points: 1,570, Visits: 680
Another point - the question specifies TINYINT, none of the queries return TINYINT. (1) is INT, (2) is DECIMAL, (3) is INT. Not only are the queries inaccurate, but none of them fit the criteria ...


Post #465157
Posted Thursday, March 6, 2008 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 27, 2009 12:26 PM
Points: 228, Visits: 61
sorry the 3rd answer is not 100% correct as written

DECLARE @DateOfBirth DATETIME
SET @DateOfBirth='08/xx/1975' --commented out the day just because

SELECT DATEDIFF(yy, @DateOfBirth, GETDATE()) AS [first],
FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0) AS [second],
DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END AS [third]

the result when it runs
first second third
33 32 33

Don't age me before my time, only the second one was correct.
Post #465164
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse