다음을 통해 공유

TSQL - Solve it YOUR Way - Finding a Person's Current Age Based on Birth Date

  • 아티클

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today's topic will cover a question asked recently in the T-SQL MSDN forums where the solution, at first, seems extremely obvious.  However, as solutions were proposed and flaws were discovered with the proposals, the interesting nature of the problem was revealed.  As is the theme of the blog series, different solutions and explanations are provided from some of the most helpful and creative contributors in the TSQL MSDN forums.  This week, the contributors are Steven Wang, Olaf Helper, Tom Cooper, and Monica Rivera.

Topic: Determine the age of a person given their birth date.

A fun aspect to this question is the fact that we've all probably made these calculations hundreds of times in our minds throughout our lives as we hear birth dates in conversation so often.  The result is that it seems that the calculation should be so simple.  However, since SQL Server does not have an AGE() function that takes a datetime parameter and returns an age, we'll need to handle the logic and calculate it ourselves.  You can then store the result as a function in your SQL Server DBA/DB Developer toolbox and have the solution forever.  Three of the below solutions are somewhat similar using some variation of DATEFIFF(), but vary slightly in interesting ways, while one (Steven Wang's solution) takes on a completely new and interesting approach.  This is an outstanding example of why I've enjoyed collaborating with other SQL Server professional on this series to highlight different methods and solutions to a single problem.  The solutions show the true power of SQL Server as a programming language and also highlight the freedom that we as DBA's, DB developers, BI experts, etc. have in creating our solutions.  I hope you enjoy the solutions as much as I have.

Solution #1 - Provided by Olaf Helper 

 

Code Snippet

  1. DECLARE @reportDate datetime;
  2. SET @reportDate = CONVERT(date,getdate());
  3.  
  4. ;WITH
  5.   birthdates AS
  6.    (SELECT 'Peter' AS Name, {d N'1970-09-14'} AS Birthdate UNION ALL
  7.     SELECT 'Bob' AS Name, {d N'1970-11-27'} AS Birthdate UNION ALL
  8.     SELECT 'Sabina' AS Name, {d N'1970-11-28'} AS Birthdate UNION ALL
  9.     SELECT 'Amy' AS Name, {d N'1970-11-29'} AS Birthdate UNION ALL
  10.     SELECT 'Jane' AS Name,  {d N'1970-12-31'} AS Birthdate UNION ALL
  11.     SELECT 'Julie' AS Name, {d N'1971-12-31'} AS Birthdate)
  12. ,age AS
  13.    (SELECT *
  14.           ,DATEDIFF(Year, Birthdate, @reportDate) AS Age
  15.           ,DATEADD(Year, DATEDIFF(Year, Birthdate, @reportDate), BirthDate) AS SwitchDate
  16.     FROM birthdates)
  17.  
  18. SELECT age.Name
  19.       ,age.Birthdate
  20.       ,CASE WHEN SwitchDate <= @reportDate
  21.             THEN age
  22.             ELSE age - 1 END AS Age
  23.       ,CASE WHEN SwitchDate = @reportDate
  24.             THEN 'Happy Birthday!!!'
  25.             ELSE '' END AS Gratulation
  26. FROM age
  27. ORDER BY age.Birthdate;

 

Explanation of Olaf's solution:

The CTE "birthdates" creates sample data around the current date for reporting, plus some additional data to check the result.

The DateDiff with option "YEAR" function seems the first and obvious way to calculate the age, but it returns only the difference of the year part and doesn't account for the additional months.
For example:

SELECT DATEDIFF(YEAR, {d N'2000-01-01'}, {d N'2001-01-01'})
      ,DATEDIFF(YEAR, {d N'2000-01-01'}, {d N'2001-12-31'})

This returns 1 for both results, even though in the second example, the difference is "nearly" 2 years.

To solve this I still use the DateDiff function, then add the difference between the birth and the given date to the birthdate. In a CASE WHEN statement I compare the result with the given date and if it's higher, I subtract 1 to get the real age.

Solution #2 - Provided by Tom Cooper 

 

Code Snippet

  1. Declare @Test Table(EmpName varchar(40), BirthDate date);
  2. Insert @Test(EmpName, BirthDate) Values
  3. ('30 Yrs old yesterday', DateAdd(day, -1, DateAdd(year, -30, GetDate()))),
  4. ('30 Yrs old today', DateAdd(year, -30, GetDate())),
  5. ('30 Yrs old tomorrow', DateAdd(day, 1, DateAdd(year, -30, GetDate())));
  6.  
  7. With cte As
  8. (Select EmpName, BirthDate, DateDiff(year, BirthDate, GetDate()) As ProvisionalAge
  9. From @Test)
  10. Select EmpName, BirthDate,
  11.   ProvisionalAge - Case When DateAdd(year, ProvisionalAge, BirthDate) > GetDate() Then 1 Else 0 End As Age
  12. From cte;

 

Explanation of Tom's solution:

This uses the fact that when you do a DATEDIFF() in years between birth date and the current date, the number you get is either the correct age or one year too large depending on whether or not the person has had a birthday in this year.  That can be determined by adding that number of years to the birth date and checking to see if you get a date greater than today.  If so, subtract 1. 

 

Solution #3 - Provided by Monica Rivera 

 

Code Snippet

  1. USE AdventureWorks
  2. GO
  3.  
  4. select LoginID, BirthDate,
  5. CASE WHEN DATEADD(YY,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate)<GETDATE() THEN DATEDIFF(yy,BirthDate,GETDATE())
  6. ELSE DATEDIFF(yy,BirthDate,GETDATE())-1 END AS AGE
  7. from HumanResources.Employee
  8. order by BirthDate

 

Explanation of Monica's solution:

There are two cases when calculating a person’s age:

1.  The birth date has already happened in the current year: the age is simply the current year minus the year of birth.
2.  The birth date has not yet happened in the current year: the age is equal to the current year minus the year of birth minus one.

A simple T-SQL expression to determine if the birth date has already happened in the current year is the following:

DATEADD(YY,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate)<GETDATE()

This adds to the birth date the difference in years between to the birth date and today’s date. If this exceeds today’s date, then we are in case 2.

Plug it in to a case statement that describes the two cases above and you get the solution.

 

Solution #4 - Provided by Steven Wang 

 

Code Snippet

  1. --Extract some Test Data using the AdventureWorks sample database
  2. If Object_ID('dbo.employee_birthdate') is not Null
  3. Drop table dbo.employee_birthdate;
  4.   SELECT[NationalIDNumber]
  5.   , [BirthDate]
  6. Into dbo.employee_birthdate
  7. FROM [HumanResources].[Employee]
  8. Union All
  9. Select -111111, '2000-02-29'
  10. Union All
  11. Select -222222, '2004-02-29'
  12. Union All
  13. Select -333333, Cast(Getdate() as Date);
  14. --Actual solution
  15. SELECT [NationalIDNumber], [BirthDate]
  16.   ,(Convert(Char(8), CURRENT_TIMESTAMP,112) - 0 - Convert(char(8), [BirthDate], 112)) / 10000 As Age
  17. FROM [dbo].[employee_birthdate]
  18. Order by Age;

 

Explanation of Steven's solution:

  1. To calculate the age based on birth date needs to take account of 2 considerations: year difference and the date was born.
  2. Use the year difference as the base of the age calculation. If the current date is less than the date was born, then the age needs to minus 1 year.
  3. Inspired by the dimensional surrogate integer date key, we can use the date integer format to perform the calculation. For example, if a birthdate is ‘2001-09-30’, and current date is ‘2012-10-01’, then we can use the difference (20121001 – 20010930) / 10000 to get the age 11. As in T-SQL Integer / Integer is always Integer. In the same example, if the current date is ‘2012-09-29’, then the result (20120929 – 20010930) / 10000 will be 10.
  4. As we can’t directly convert the date format to integer value, I convert the date to char(8) first and use minus 0 to implicitly convert the char value to integer value.

Conclusion:

As you can see, all four of the above solutions provide the result we were looking for, but do so in creatively different styles.  Each of these solutions highlights the power and flexibility that SQL Server provides. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Olaf, Tom, Monica, and Steven for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Contributor Bios:

Steven Wang has worked with SQL server for more than 10 years. Steven is an active SQL server community participant and speaks at events such as TechEd, CodeCamp, SQL User Group etc.

Blog: www.MSBICOE.com | LinkedIn: Steven Wang | MSDN Profile: Steven Wang - Shangzhou

Tom Cooper began his programming career in 1968, began working with database software in 1977, and first worked with Microsoft SQL Server in 1994 (version 4.21).  He is now very happily retired.

Monica Rivera has been working with SQL Server for the past 10 years. She worked as a DBA before joining Microsoft, where she works as a tester for the SQL Server Enterprise & Tier 1 Manageability group.  She is very active in both the English and Spanish SQL Server MSDN forums.

Comments

  • Anonymous
    November 30, 2012
    Samuel Lester, a senior tester in the SQL Server team is writting a blog series about how to solve common

  • Anonymous
    May 09, 2013
    Thanks a lot for collecting together &  sharing, Sam!

  • Anonymous
    June 07, 2013
    Hi Sam, Can you please provide solution for C# and with fractional numbers? I need to calculate age with fractions. Thanks in advance.

  • Anonymous
    June 07, 2013
    With some help found perfect solution here http://snipplr.com/view/18560/

  • Anonymous
    December 29, 2013
    Thank you so much for valuable info.

  • Anonymous
    March 04, 2014
    The comment has been removed

  • Anonymous
    March 05, 2014
    Hi JK, thanks for the positive feedback.  I agree, the code plug-in I'm using looks nice, but makes copy/paste a bit of a challenge.  Are you familiar with the rectangular text selection option in SSMS (& VS) using Shift + Alt + arrow keys?  This is what I use after pasting code from this series.  Paste in the code, go to a top corner of the numbers, hold down Shift + Alt and use the arrows to form the rectangular selection around the numbers, then delete.  You can also use this to copy/paste rectangular code blocks, which also comes in handy.

  • Anonymous
    August 25, 2014
    Sam, Excellent write-up, thanks for compiling. I was doing some analysis to see which version I thought was the most accurate and it is looking like they are all equivalent except for Monica's solution, which should have a <= instead of a <. I'll post my testing script below for others. -Haakon

  • Anonymous
    August 26, 2014
    I wasn't able how to post my test code due to some restrictions, but I ran all dates from 1950 through 2014 (about 100M permutations) and looked for discrepancies with the 4 sets.  Below is code for the two outliers I noticed.  Steve's solution appeared to be the one that handled both cases properly. -Haakon /* Should be 0 Years old, but only Steve and Monica show 0 / DECLARE @BirthDate AS DATE = '2012-02-29' DECLARE @ReportDate AS DATE = '2013-02-28' / Should be 1 Years old, but Monica shows 1 */ --SET @BirthDate = '2012-01-01' --SET @ReportDate = '2013-01-01' SELECT  CASE WHEN DATEADD(Year, DATEDIFF(Year, @BirthDate, @ReportDate), @BirthDate) <= @ReportDate THEN DATEDIFF(Year, @BirthDate, @ReportDate)             ELSE DATEDIFF(Year, @BirthDate, @ReportDate) - 1        END AS 'OlafSolution' ,        DATEDIFF(year, @BirthDate, @ReportDate) - CASE WHEN DATEADD(year, DATEDIFF(year, @BirthDate, @ReportDate), @BirthDate) > @ReportDate THEN 1                                                       ELSE 0                                                  END AS 'TomSolution' ,        CASE WHEN DATEADD(YY, DATEDIFF(yy, @BirthDate, @ReportDate), @BirthDate) < @ReportDate THEN DATEDIFF(yy, @BirthDate, @ReportDate)             ELSE DATEDIFF(yy, @BirthDate, @ReportDate) - 1        END AS 'MonicaSolution' ,        ( CONVERT(CHAR(8), @ReportDate, 112) - 0 - CONVERT(CHAR(8), @BirthDate, 112) ) / 10000 AS 'StevenSolution'

  • Anonymous
    April 15, 2016
    USE [livedb];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOCREATE FUNCTION dbo.GetAge(@DOB DATETIME, @ToDate DATETIME)RETURNS SMALLINTASBEGIN DECLARE @Diff SMALLINT, @F DATETIME, @T DATETIME SELECT @Diff = DATEDIFF(month, @DOB, @ToDate) / 12, @F = DATEADD(year, 2000 - DATEPART(year, @DOB), @DOB), @T = DATEADD(year, 2000 - DATEPART(year, @ToDate), @ToDate) IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0 BEGIN IF @DOB @T SELECT @Diff = @Diff - 1 IF @DOB > @ToDate AND @F < @T SELECT @Diff = @Diff + 1 END RETURN @DiffEND

  • Anonymous
    April 19, 2016
    Select DATEDIFF(yy, BirthDate, GETDATE()) - CASE WHEN MONTH(BirthDate) > MONTH(GETDATE()) OR (MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE())) THEN 1 ELSE 0 END

  • Anonymous
    December 14, 2016
    Can any one help me to get the age on as on date with years months and days in a single query?

  • Anonymous
    March 15, 2019
    This is a solution without case:select YEAR(Getdate())-YEAR(DATEADD(dd,-DAY(GETDATE()),DATEADD(mm,-MONTH(GETDATE())+1,'1978/09/02')))-1