January 8, 2013 at 9:33 am
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
January 8, 2013 at 9:42 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 11:18 am
Thank You Sean
That worked perfectly.
Greatly appreciate it
Andrew SQLDBA
January 8, 2013 at 11:27 am
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,
January 8, 2013 at 12:13 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 12:16 pm
What about this: http://www.sqlservercentral.com/articles/T-SQL/63351/.
January 8, 2013 at 1:16 pm
What is STPRD.STPR_START_DATE? Student Program Start Date
January 8, 2013 at 1:23 pm
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
January 8, 2013 at 2:29 pm
mdsharif532 (1/8/2013)
DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIMESET @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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 2:54 pm
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
January 8, 2013 at 3:07 pm
Actually I think that a negative age has some real value if you think of age not as only the age of a person. What you are calculating is the total years elapsed between two dates and if you were looking at something like project delivery dates a negative data might have some real worth. Maybe a company that works with long term deliverables like construction. It might be nice to see that something was delivered more than a year ahead of schedule. Your fine code would work for such a scenario if it included negatives.
We have now seen several different approaches to the same problem. Most of them handle leap years correctly too. 😀
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 3:34 pm
Sean Lange (1/8/2013)
Actually I think that a negative age has some real value if you think of age not as only the age of a person. What you are calculating is the total years elapsed between two dates and if you were looking at something like project delivery dates a negative data might have some real worth. Maybe a company that works with long term deliverables like construction. It might be nice to see that something was delivered more than a year ahead of schedule. Your fine code would work for such a scenario if it included negatives.We have now seen several different approaches to the same problem. Most of them handle leap years correctly too. 😀
Going by the dictionary definition of age, "The length of time during which a being or thing has existed.", I have to say that negative age does not make much sense to me.
I tested the solutions from the other posts using the test data I posted (leaving out the negative ages), and found every one had at least one difference with the solution I posted, especially with the handling of Feb 29 birthdays, or when the time of day for CURR_DATE was before the time of day for DOB when they were both the same day of the year ( Example: DOB = 2013-01-08 16:52:54.810 and CURR_DATE = 2023-01-08 16:52:54.710 ). I believe most calculations of Age ignore time of day, so my solution is coded to ignore it.
January 9, 2013 at 7:22 am
Going by the dictionary definition of age, "The length of time during which a being or thing has existed.", I have to say that negative age does not make much sense to me.
I agree that as age negative doesn't make any sense. I was trying to point out the calculation could be used in other scenarios where a negative would make sense. Given that the thread is to calculate human age it certainly doesn't make any sense in that context.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2013 at 8:03 am
AndrewSQLDBA (1/8/2013)
Hello EveryoneHappy 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
So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2013 at 9:05 am
Jeff Moden (1/9/2013)
AndrewSQLDBA (1/8/2013)
Hello EveryoneHappy 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
So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? 😉
As usual, no one agrees on this, but different countries do at least have some standard.
http://en.wikipedia.org/wiki/February_29
"...in England and Wales or in Hong Kong, a person born on February 29, 1996, will have legally reached 18 years old on March 1, 2014. If he or she was born in the United States, Taiwan or New Zealand, he or she legally becomes 18 on February 28, 2014, a day earlier..."
I prefer the Feb 28 date for birthdays in non-leap years because that's the "standard" for the US. And, it's easier to code in TSQL.:-D
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply