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 3:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
Comments posted to this topic are about the item Best Way to Calculate Age

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #464861
Posted Thursday, March 6, 2008 3:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 8:13 AM
Points: 1,278, Visits: 2,204
If a person's DOB = '1988-02-24 15:00:00.000', the age should be 20 already as of today (March 5th, 2008).

But the 3rd query returns 19.

select DATEDIFF(yy, '1988-02-24 15:00:00.000', GETDATE()) - CASE WHEN DATEPART(m, '1988-02-24 15:00:00.000') >= DATEPART(m, GETDATE()) AND DATEPART(d, '1988-02-24 15:00:00.000') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

Does the 3rd query return the right age?
Post #464866
Posted Thursday, March 6, 2008 5:59 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: Tuesday, September 23, 2014 6:47 PM
Points: 523, Visits: 557
I ran all 3 queries with my DOB and the first and 3rd ones returned the incorrect value.

The 2nd query had the correct value.

looking at the case statement I think you have the 1 and 0 around the wrong way
Post #464904
Posted Thursday, March 6, 2008 6:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 29, 2010 1:54 AM
Points: 986, Visits: 44
I also ran all 3 queries for my DOB and 2nd was correct, both 1st and 3rd were wrong.
Post #464907
Posted Thursday, March 6, 2008 6:36 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:31 AM
Points: 491, Visits: 225
The first and second answers were correct for me. The third answer was definitely wrong in my case.
Post #465087
Posted Thursday, March 6, 2008 6:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:23 AM
Points: 1,019, Visits: 446
Jumping on the bandwagon here: only the second statement returned the correct value for my birthdate. Gimme my point! ARGH!

:P


- Tim Ford, SQL Server MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Post #465090
Posted Thursday, March 6, 2008 6:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 4:40 PM
Points: 654, Visits: 375
Howdy, thanks for the interesting question.

The 3rd answer was wrong for my DOB. It showed a younger value though, which is appreciated.
Post #464916
Posted Thursday, March 6, 2008 6:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 19, 2012 6:54 AM
Points: 138, Visits: 137
Definitely the second answer!
Post #465099
Posted Thursday, March 6, 2008 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 18, 2013 8:20 AM
Points: 277, Visits: 410
In my case, like many others, the second answer was the only correct one. My birthday is not until September.
Post #465114
Posted Thursday, March 6, 2008 6:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:23 PM
Points: 116, Visits: 55
The third solution returns "51" for DOB 08/21/1957.

select DATEDIFF(yy, '08/21/1957', GETDATE()) - CASE WHEN DATEPART(mm, '08/21/1957') >= DATEPART(mm, GETDATE()) AND DATEPART(d, '08/21/1957') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END

Clearly, this is INCORRECT.
Post #465115
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse