sql age function

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+2  |  -0

If you are after proper T-SQL Age calculation or Function then the below post might help you.

Below are questions that I try to answer in this post:

  • How to get age from birth date using getdate() (current date) in Tsql
  • How to calculate age from data of birth (DOB) and specified date
  • How to create T-SQL aging UDF function to calculate age from DOB and today's date or specified date
  • Calculate person's age (Accurate age calculation)

    T-SQL Age - years difference

    T-SQL Date of birth to Age



    T-SQL Age Calculation
     

    T-SQL Age Function (UDF)



    T-SQL Age Function

    T-SQL Age Calculation
    • There are many example on the web of inaccurate calculation of age or date of birth. Some of them calculate it without taking into consideration month and day of month. Here is a link to one of these pages T-SQL Age (inaccurate) which tries to calculate age by dividing it by 365.25.

      Sometimes you might be after just a way to calculate difference in years and below is an example that you can use:

      SELECT DATEDIFF(Year,'1983-08-19',GETDATE()) AS AGE -- THIS IS DIFFERENCE IN YEARS

      But more ofter you want to calculate precise age and not accurate in 99% of cases but accurate in 100% of cases and below is how you can do that.

      If you would like to calcualte person's age using t-sql by converting person's date of birth to age then the below t-sql statement might help you.

      -- first we declare date of birth and the date we want to compare it to (usually today's date).
      DECLARE @DOB AS DATETIME
      SET @DOB = '1983-08-19'
      DECLARE @CompareDate AS DATETIME
      SET @CompareDate = GETDATE()

      -- below is t-sql statement that performs the actual conversion from date of birt to age.
      SELECT CASE WHEN (MONTH(@CompareDate)*100)+DAY(@CompareDate) >= (MONTH(@DOB)*100)+DAY(@DOB)
      THEN DATEDIFF(Year,@DOB,@CompareDate) ELSE DATEDIFF(Year,@DOB,@CompareDate)-1 END AS Age
      -- below example is the same above but based on field from database and getdate()
      SELECT CASE WHEN (MONTH(GETDATE())*100)+DAY(GETDATE()) >= (MONTH(DOB_Field)*100)+DAY(DOB_Field)
      THEN DATEDIFF(Year,DOB_Field,GETDATE()) ELSE DATEDIFF(Year,DOB_Field,GETDATE())-1 END AS Age

      -- and another t-sql statement but based on both dob field from database and comparedate from database as well.
      SELECT CASE WHEN (MONTH(CompareDate_Field)*100)+DAY(CompareDate_Field) >= (MONTH(DOB_Field)*100)+DAY(DOB_Field)
      THEN DATEDIFF(Year,DOB_Field,CompareDate_Field) ELSE DATEDIFF(Year,DOB_Field,CompareDate_Field)-1 END AS Age

      The link provides an alternative calculation T-SQL Age (also accurate)
      Below is T-SQL Age Function (UDF) which you can use and modify. Please be aware that it was created using SQL Server 2008 R2 function so it might not work on 2008 or 2005 without version adjustments.
       

CREATE FUNCTION [dbo].[udfCalculateAge]
(
@DOB AS DATE,
@EndDate as DATE = '2999-01-01' -- Defaul is today's date (see below) but any date can be used here
)
RETURNS TINYINT
AS
BEGIN
DECLARE @Result as TINYINT

-- IF DEFAULT VALUE (marked as 2999-01-01 as it doesn't accept functions) IS USED THEN USE TODAY'S DATE
IF @EndDate = '2999-01-01'
    SET @EndDate = GETDATE()

IF @DOB >= @EndDate -- trap errors
    SET @Result = 0
ELSE
    BEGIN
    -- check if the person had its birthday in the specified year and calculate age
        IF (MONTH(@EndDate)*100)+DAY(@EndDate) >= (MONTH(@DOB)*100)+DAY(@DOB)
            SET @Result = DATEDIFF(Year,@DOB,@EndDate)
        ELSE
            SET @Result = DATEDIFF(Year,@DOB,@EndDate)-1
        --END IF
    END

RETURN @Result

END

And below you can see the results

Related Internal Links:
T-SQL Current Date Related External Links:
T-SQL DateDiff function
T-SQL Day function
T-SQL Month function
Age (Wikipedia)
Ageing (Wikipedia)


Hope that helps!
Emil


Remove Ads or Log in

We need you!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Visitor comment (we like):
"I like you both, you read Dale Carnagie, you want what most of us want and you are trying to do something useful for all! You are already successful! Blessings!"
Add comment (no email required).



Add Comment
Name: pradeep
Date Posted: 7/16/2012 2:33:28 PM
Comment:

I just tried with the below query to find the age from date of birth.

select empid,empname,date_of_birth,convert(int,GETDATE()-date_of_birth)/365 as "age" from dbo.employee

 

Thanks,

Pradeep




Name: R. Schreurs
Date Posted: 2/5/2013 11:37:07 AM
Comment:

Thanks for the post.

I was almost going to use:

if(datepart(y, @enddate) >= datepart(y, @dob))

but this fails in leap years when the person's date of birth is after 29th of february. Such a date is not the same day number as the same date in non-leap years.

You solution works correct.
 




Name: hrothenb
Date Posted: 3/20/2013 8:03:05 PM
Comment:

 

DECLARE @DOB DATETIME,

@later DATETIME

SELECT @DOB = '02/07/1980',

@later = '02/06/1981'

SELECT DATEDIFF(yy, @DOB, @later) -

CASE

WHEN @DOB <= DATEADD(yy, DATEDIFF(yy, @later, @DOB), @later) THEN 0

ELSE 1

END AS Age

 

SELECT CASE WHEN datepart(dayofyear, @later ) >= datepart(dayofyear, @DOB)

THEN DATEDIFF(yy,@DOB,@later)

ELSE DATEDIFF(yy,@DOB,@later) -1

END AGE

 

 

SELECT CASE

WHEN DATEPART(dayofyear, GETDATE()) >= DATEPART(dayofyear, @DOB) THEN

DATEDIFF(yy, @DOB, GETDATE())

ELSE DATEDIFF(yy, @DOB, GETDATE()) -1

END AGE

 

 






Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits
  Become a Proud Member