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 Tuesday, March 11, 2008 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 7, 2010 11:58 AM
Points: 9, Visits: 80
Its not only Taiwan, England and Wales also puts the legal birthday of leaplings as Feb 28. I think it has to do with trying to keep the birthday in February.



Post #467701
Posted Tuesday, March 11, 2008 3:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
ScottPletcher (3/11/2008)
why does it make more sense for the birthday to be after their birthday than it does to have it be before?


Because then the exact same check that works for everyone else would work for them. No additional coding/checks required. Otherwise a large number of specific Feb 29/28 checks must be coded into age check ... yuck.

And I quite agree with Hugo: my common sense tells me the birthday cannot occur on a date before it occurs.


Amusingly enough, my function was being critiqued for NOT having special clauses for Feb 29 birthdays. The code I used does not require special handlings for them. It uses native SQL functions (datediff and dateadd) on datetime data types.

In order to get the results you want, 1 March instead of 28 Feb in non-leap years, you actually have to add more code to the solution.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #467790
Posted Wednesday, March 12, 2008 9:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:01 AM
Points: 357, Visits: 71
All of them are incorrect. I think the correct age will be:

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
Post #468220
Posted Wednesday, March 12, 2008 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:14 PM
Points: 9, Visits: 61
The "real point" was that the author should have tested before posting. The topic was already full of "solutions" (many of them flawed), so why would I beat a dead horse?


Post #468228
Posted Wednesday, March 12, 2008 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:53 PM
Points: 173, Visits: 424
lol, as I see I am not the only one angry at the official answer....

the following 2 statements both return 27 years old...

select DATEDIFF(yy, '1/1/1980', GETDATE()) - CASE WHEN DATEPART(m, '1/1/1980') >= DATEPART(m, GETDATE()) AND DATEPART(d, '1/1/1980') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
select DATEDIFF(yy, '11/11/1980', GETDATE()) - CASE WHEN DATEPART(m, '11/11/1980') >= DATEPART(m, GETDATE()) AND DATEPART(d, '11/11/1980') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END




I want my point back!
Post #468413
Posted Friday, March 14, 2008 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 8:00 AM
Points: 3, Visits: 25
The following formula could be more accurate


(DATEDIFF(yy, @dob, getdate()) - CASE
WHEN DATEPART(m, @dob) < DATEPART(m, getdate()) THEN 0
WHEN DATEPART(m, @dob) = DATEPART(m, getdate()) AND DATEPART(d, @dob) <= DATEPART(d, getdate()) THEN 0
ELSE 1 END) end



Hank Cao
Post #469524
Posted Friday, March 14, 2008 10:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:24 AM
Points: 1,145, Visits: 105
Using my DOB, None of the 3 technically gave the correct answer.
WHen I ran it my DOB was 4 days in the future

Setting my DOB to 2 months, 4 days in the future, this statement was the ONLY one that was correct:
FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0)



Post #469532
Posted Sunday, March 16, 2008 1:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 4:07 PM
Points: 31, Visits: 60
I eliminated numbers 1 and 3 in my head since it didn't calculate my age correctly, and picked 2. Oh well.

Anyway, it looks like number 3 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

(changed the >= on the day comparison to > since most people count their birthday as the day they get older, and not the day after.)
Post #469964
Posted Monday, March 17, 2008 2:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2008 12:51 PM
Points: 43, Visits: 7
Indeed, the 0 and the 1 should be reversed. However, if you plug in a birthdate that occurs on the current Month/Day, it is incorrect.

Post #470492
Posted Wednesday, March 26, 2008 4:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2008 9:54 AM
Points: 17, Visits: 20
Hello,

The "AND" in de case is clearly wrong because if the day of the BirthDay is inferior to the one obtain in the GETDATE() it will always return 0.
CASE WHEN DATEPART(m, BirthDate) >= DATEPART(m, GETDATE()) AND DATEPART(d, BirthDate) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
Post #474608
« Prev Topic | Next Topic »

Add to briefcase «««1516171819»»»

Permissions Expand / Collapse