SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to Calculate Human Age from a Birthdate


Best way to Calculate Human Age from a Birthdate

Author
Message
AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1752 Visits: 3427
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25843 Visits: 17509
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1752 Visits: 3427
Thank You Sean

That worked perfectly.

Greatly appreciate it

Andrew SQLDBA
mdsharif532
mdsharif532
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 139
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,
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25843 Visits: 17509
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38961 Visits: 38508
What about this: http://www.sqlservercentral.com/articles/T-SQL/63351/.

Cool
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)
mdsharif532
mdsharif532
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 139
What is STPRD.STPR_START_DATE? Student Program Start Date
mdsharif532
mdsharif532
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 139
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25843 Visits: 17509
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. :-D

_______________________________________________________________

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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5680 Visits: 11771
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search