Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Best way to Calculate Human Age from a...
Add to briefcase
20 posts, Page 1 of 2
12»»

Best way to Calculate Human Age from a Birthdate Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
AndrewSQLDBA
Posted Tuesday, January 08, 2013 9:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 796, Visits: 2,279
Hello Everyone
Happy New Year!!!

I am fooling around with some code, and was wondering if there is a really great function to calculate the birthdate of a person. One that will take into count things like leap year. I am not getting into such fine details such as if the person was born on the west coast or the east coast type of calculations. Just a very good birthdate age calculation function.

If I pass in the birthdate, what is the persons age today. Most that I have used or tried, come up a bit short when it comes to returning a perfect calculation

Thanks in Advance
Andrew SQLDBA
Post #1404342
Sean Lange
Posted Tuesday, January 08, 2013 9:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273
There are tons of articles and forum posts around here. This is about the best in my opinion.

http://www.sqlservercentral.com/Forums/Topic796803-338-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1404347
AndrewSQLDBA
Posted Tuesday, January 08, 2013 11:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 796, Visits: 2,279
Thank You Sean

That worked perfectly.

Greatly appreciate it

Andrew SQLDBA
Post #1404394
mdsharif532
Posted Tuesday, January 08, 2013 11:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:09 PM
Points: 14, Visits: 97
DATEDIFF(YEAR,P.BIRTH_DATE,STPRD.STPR_START_DATE) - CASE WHEN MONTH(P.BIRTH_DATE)*100 + DAY(P.BIRTH_DATE) > MONTH(STPRD.STPR_START_DATE)*100 + DAY(STPRD.STPR_START_DATE) THEN 1 ELSE 0 END AS AGE,
Post #1404399
Sean Lange
Posted Tuesday, January 08, 2013 12:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273
mdsharif532 (1/8/2013)
DATEDIFF(YEAR,P.BIRTH_DATE,STPRD.STPR_START_DATE) - CASE WHEN MONTH(P.BIRTH_DATE)*100 + DAY(P.BIRTH_DATE) > MONTH(STPRD.STPR_START_DATE)*100 + DAY(STPRD.STPR_START_DATE) THEN 1 ELSE 0 END AS AGE,


You really should look at the link I posted. It is simple, accurate and easy to understand which the above is not.

This looks like you took this from a query you have in your system somewhere. What is STPRD.STPR_START_DATE?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1404421
Lynn Pettis
Posted Tuesday, January 08, 2013 12:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,627, Visits: 27,480
What about this: http://www.sqlservercentral.com/articles/T-SQL/63351/.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1404422
mdsharif532
Posted Tuesday, January 08, 2013 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:09 PM
Points: 14, Visits: 97
What is STPRD.STPR_START_DATE? Student Program Start Date
Post #1404439
mdsharif532
Posted Tuesday, January 08, 2013 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:09 PM
Points: 14, Visits: 97
DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '1975-01-07'
SET @STPR_START_DATE = '2013-01-06'

SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE)
Result: 38

SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END AS AGE
Result: 37
Post #1404440
Sean Lange
Posted Tuesday, January 08, 2013 2:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273
mdsharif532 (1/8/2013)
DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '1975-01-07'
SET @STPR_START_DATE = '2013-01-06'

SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE)
Result: 38

SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END AS AGE
Result: 37


OK now it makes sense. I just couldn't figure out what the dates were supposed to be.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1404449
Michael Valentine Jones
Posted Tuesday, January 08, 2013 2:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517
The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.

Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years.

The concept of negative age doesn't seem to have any real world meaning, so my method returns null if the date of birth is before current date.

select 
	a.DOB,
	a.CURR_DATE,
	Age =
	datediff(yy,a.DOB,a.CURR_DATE) +
	case
	-- Age is null when DOB before current date
	when datediff(dd,a.DOB,a.CURR_DATE) < 0
	then null
	-- Subtract 1 if current date before birthday in current year
	when datediff(dd,dateadd(yy,datediff(yy,a.DOB,a.CURR_DATE),a.DOB),a.CURR_DATE) < 0
	then -1 else 0 end
from
	( -- Test Data
	select
		DOB =convert(datetime,'20040407'),
		CURR_DATE = convert(datetime,'20060203')
	union all
	select getdate(),dateadd(ms,100,getdate())  union all
	select getdate(),dateadd(ms,-100,getdate())  union all
	select getdate(),dateadd(dd,-1,getdate())  union all

	select getdate(),dateadd(yy,10,dateadd(ms,-100,getdate()))  union all
	select getdate(),dateadd(yy,10,dateadd(ms,100,getdate()))  union all

	select '20040407','20040407'  union all
	select '20040407','20050406'  union all
	select '20040407','20050407'  union all
	select '20040229','20060227'  union all
	select '20040229','20060228'  union all
	select '20040229','20060301'  union all
	select '20040229','20080228'  union all
	select '20040229','20080229'  union all
	select '20060205','20060205'  union all
	select '17530101 00:00:00.000','99991231 23:59:59.997'  union all
	select '19500913', getdate()
	) a
order by
	a.DOB,
	a.CURR_DATE

Results:
DOB                     CURR_DATE                       Age
----------------------- ----------------------- -----------
1753-01-01 00:00:00.000 9999-12-31 23:59:59.997        8246
1950-09-13 00:00:00.000 2013-01-08 16:52:54.810          62
2004-02-29 00:00:00.000 2006-02-27 00:00:00.000           1
2004-02-29 00:00:00.000 2006-02-28 00:00:00.000           2
2004-02-29 00:00:00.000 2006-03-01 00:00:00.000           2
2004-02-29 00:00:00.000 2008-02-28 00:00:00.000           3
2004-02-29 00:00:00.000 2008-02-29 00:00:00.000           4
2004-04-07 00:00:00.000 2004-04-07 00:00:00.000           0
2004-04-07 00:00:00.000 2005-04-06 00:00:00.000           0
2004-04-07 00:00:00.000 2005-04-07 00:00:00.000           1
2004-04-07 00:00:00.000 2006-02-03 00:00:00.000           1
2006-02-05 00:00:00.000 2006-02-05 00:00:00.000           0
2013-01-08 16:52:54.810 2013-01-07 16:52:54.810        NULL
2013-01-08 16:52:54.810 2013-01-08 16:52:54.710           0
2013-01-08 16:52:54.810 2013-01-08 16:52:54.910           0
2013-01-08 16:52:54.810 2023-01-08 16:52:54.710          10
2013-01-08 16:52:54.810 2023-01-08 16:52:54.910          10
Post #1404457
« Prev Topic | Next Topic »

Add to briefcase
20 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.