# Calculating age using sql server function

### Question

• Hi ya,

I have a table which will be having DOB and I want to calculate the current age of persons, i want to save it into currentage as default.

I have searched over the interent and find some solutions but in my database the DOB is stored as = 'dd/mm/yyyy' and none of the solutions work on this.

Any ideas?

Wednesday, September 27, 2006 12:23 PM

• ```FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25)
```
is simple but not precise.  For example a person born on 29 -May-1992 would be 17 on 29-May-2009, but this formula returns 16.
------------ The actual number of days in a year is 365.242199 so this one should work for just about all age calculation
```FLOOR(DATEDIFF(day,DOB,GETDATE())/365.242199)
```

theduke
Friday, September 17, 2010 1:32 AM
• First, create this user defined function.

###### ALTER function [dbo].[fn_GetAge]

(@in_DOB AS datetime,@now as datetime)

returns int

as

begin

DECLARE @age int

IF cast(datepart(m,@now) as int) > cast(datepart(m,@in_DOB) as int)

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)

else

IF cast(datepart(m,@now) as int) = cast(datepart(m,@in_DOB) as int)

IF datepart(d,@now) >= datepart(d,@in_DOB)

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)

ELSE

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) -1

ELSE

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) - 1

RETURN @age

end

----------------------------------

Then, to use it...

Select DOB, dbo.fn_GetAge(DOB, GetDate()) As Age From Table

Since all calculations are based on DateTime's, your formatting shouldn't matter

Wednesday, September 27, 2006 2:23 PM
• I found this thread while trying to anwser the same.  I got to the same answer as Simon H but also found the exceptions where it does not work caused by leap years.

I came up with .....

declare @DOB datetime              Set  @DOB = '2004-02-29'

declare @CompareDate datetime       Set  @CompareDate = '2005-02-28' -- Getate()

Select

case

when DATEADD(YY, DATEDIFF(YY, @DOB , @CompareDate),  @DOB )  > @CompareDate

then DATEDIFF(YY, @DOB , @CompareDate) - 1

else DATEDIFF(YY, @DOB , @CompareDate)

end

Strange logic I know:

·     Compare the years

·     If result is greater than Today (i.e. @CompareDate) then subtract a Year because you have over shot.

As far as I can tell it works for all dates including leap years, Feb 29th, etc. Its really using the same principles as SQL Slave and some others but a different "tack", but possibly even stranger logic, but only 4 functions:) and only three used for any scenario.

One point of interest is that adding one year to Feb 29th (of a leap year) using DATEADD gives Feb 28th. Whereas adding four years gives Feb 29th of the following leap year.  Makes sense I guess. In other words it assumes someone born on Feb 29th celibrates that day on other leap years and on Feb 28th for the "off" years.

Thursday, October 22, 2009 11:52 PM
• Hi

I used *100 trick to simplify the calcualation and still get correct result http://www.itcouple.co.uk/t-sql-age.aspx

Regards

Emil

Sunday, February 06, 2011 10:15 AM
• declare

@d1

datetime

set

@d1 = convert(datetime,'09/02/2001')

;

select

case when convert(datetime, datename(mm,@d1) + ',' + DATENAME(day,@d1)+ ' '+ DATENAME(year,Getdate

()))

< = getdate() then DATEDIFF(year,@d1, getdate())

else DATEDIFF(year,@d1 , getdate()) - 1

end age
Thursday, September 01, 2011 12:52 PM

### All replies

•  Mugambo wrote:
 Prince: I assume by your post that your DOB is stored as a string.  Try: select datediff (year, convert (datetime, '07/15/1908'), getdate())   Dave

Hello Dave,

No you got me wrong, the DOB is stored as Datetime in Sql 2005, the other thing is that datediff is not going to calculate right and thirdly my dates are stored as datetime but in dd/mm/yyyy format.

You can try it by yourself.

Wednesday, September 27, 2006 1:25 PM
• First, create this user defined function.

###### ALTER function [dbo].[fn_GetAge]

(@in_DOB AS datetime,@now as datetime)

returns int

as

begin

DECLARE @age int

IF cast(datepart(m,@now) as int) > cast(datepart(m,@in_DOB) as int)

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)

else

IF cast(datepart(m,@now) as int) = cast(datepart(m,@in_DOB) as int)

IF datepart(d,@now) >= datepart(d,@in_DOB)

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)

ELSE

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) -1

ELSE

SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) - 1

RETURN @age

end

----------------------------------

Then, to use it...

Select DOB, dbo.fn_GetAge(DOB, GetDate()) As Age From Table

Since all calculations are based on DateTime's, your formatting shouldn't matter

Wednesday, September 27, 2006 2:23 PM
• thanks mastros,

it works really great.

Wednesday, September 27, 2006 3:19 PM

• Hi, this should be enough...

FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25)

Regards,

Wednesday, November 12, 2008 10:08 PM
• Uh...it's way easier than this.  Using the "/" function with integer data types always rounds down (or "floors") the result:

Code Snippet

alter function dbo.fn_GetAge(

@in_DOB as DateTime

, @now as DateTime

)

returns tinyint

as

begin

return datediff(mm, @in_DOB,@now) / 12

end

Or use the table without a function at all

Code Snippet

select *, datediff(mm, DOB, getdate()) / 12 as [Age]

from <MyTable>

Wednesday, November 12, 2008 10:21 PM
•  rusag2 wrote:
 Uh...it's way easier than this.  Using the "/" function with integer data types always rounds down (or "floors") the result:     Code Snippet   alter function dbo.fn_GetAge( @in_DOB as DateTime , @now as DateTime ) returns tinyint as begin return datediff(mm, @in_DOB,@now) / 12 end       Or use the table without a function at all Code Snippet   select *, datediff(mm, DOB, getdate()) / 12 as [Age] from

Rusag,

You cant use months, as it will not produce the correct results.  SQL datediff functions do not account for days in a month, thus the datediff between last month and this month is always 1 while this is not true for birthdays.  because your age is dependent on what day of a particular month, you have to take the day into account.  The easiest method to do this is the one provided using the calculation difference in days / 365.25 (accounting for leap year).

Take a look at the example below.

Code Snippet

select datediff(mm, '2007-11-22', getdate()) / 12--1

select FLOOR(DATEDIFF(day, '2007-11-22', getDate()) / 365.25)--0

Thursday, November 13, 2008 4:18 PM
• Very nice! thanks
Thursday, December 04, 2008 2:13 PM
•  Prince of Dhump wrote:
 Hello Dave, No you got me wrong, the DOB is stored as Datetime in Sql 2005, the other thing is that datediff is not going to calculate right and thirdly my dates are stored as datetime but in dd/mm/yyyy format.

Just so you know - datetimes not stored in that format. They may be displayed in that format for you, but they are UNC values (e.g. yyyy-mm-dd hh:mms.nnn)
Thursday, December 04, 2008 2:38 PM
• try this
DECLARE

@BirthDate DATETIME

DECLARE

@Today DATETIME

SET

@BirthDate = '10/8/1981'

SET

@Today = '12/8/2009'

SELECT

CASE WHEN MONTH(@BirthDate) <= MONTH(@Today) AND DAY(@BirthDate) <= DAY(@Today)

THEN DATEDIFF(Year,@BirthDate,@Today) +1

ELSE DATEDIFF(Year,@BirthDate,@Today) END

Ramesh
Friday, September 18, 2009 1:02 PM
• OK Here's a way to do it so that a DOB field will return an age in a Select statement, and thus can be substituted directly into queries.
It's not pretty, (which is an understatement) but it works quite well considering Microsoft doesn't supply us any tools to make it otherwise.  If [DOB] is null, the expression returns NULL.  I've tested it for ages > 100 years and [DOB] in the future, for which it returns a negative age, (but doesn't return an exception).  So it's pretty safe.

NOTE: you will have to replace the variable @DOB with your field name at 3 different positions.

select

/*essentially, how it works is to make use of the datediff function for years.  But first you have to
1.  Calculate the integer that represents today, but a year ago.
2.  Subsitiute the year part of the DOB with the current year.
3.  Subtract 1 from 2
4.  Convert the result to a datetime
5.  Look at the year part of that result.  It will either be 1900 or 1901, depending on whether today comes before   or after the DOB in the calendar year.
6.  Subtract 1900 from it, so you get 0 or 1 depending on the time of year the DOB is relative to today.
7.  Use the 0 or 1 to correct the expression in the first part before the - sign, which always retuns the upper age in years between DOB and today.
8.  Crack open a beer - you deserved it.

Please don't laugh until after you have submitted a more elegant solution :)

Paul*/

Monday, September 21, 2009 8:39 PM
• Tuesday, September 22, 2009 11:30 AM
• I use a slightly shorter solution but it still requires both variables to be entered three times.
`DATEDIFF(yy,@DOB,@Today)-CASE WHEN DATEADD(yy,DATEDIFF(yy,@DOB,@Today),@DOB)>@Today THEN 1 ELSE 0 END`

By the way
`FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25) `
is simple but not precise.  For example a person born on 29 -May-1992 would be 17 on 29-May-2009, but this formula returns 16.
Friday, September 25, 2009 8:13 PM
• I found this thread while trying to anwser the same.  I got to the same answer as Simon H but also found the exceptions where it does not work caused by leap years.

I came up with .....

declare @DOB datetime              Set  @DOB = '2004-02-29'

declare @CompareDate datetime       Set  @CompareDate = '2005-02-28' -- Getate()

Select

case

when DATEADD(YY, DATEDIFF(YY, @DOB , @CompareDate),  @DOB )  > @CompareDate

then DATEDIFF(YY, @DOB , @CompareDate) - 1

else DATEDIFF(YY, @DOB , @CompareDate)

end

Strange logic I know:

·     Compare the years

·     If result is greater than Today (i.e. @CompareDate) then subtract a Year because you have over shot.

As far as I can tell it works for all dates including leap years, Feb 29th, etc. Its really using the same principles as SQL Slave and some others but a different "tack", but possibly even stranger logic, but only 4 functions:) and only three used for any scenario.

One point of interest is that adding one year to Feb 29th (of a leap year) using DATEADD gives Feb 28th. Whereas adding four years gives Feb 29th of the following leap year.  Makes sense I guess. In other words it assumes someone born on Feb 29th celibrates that day on other leap years and on Feb 28th for the "off" years.

Thursday, October 22, 2009 11:52 PM
• ```FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25)
```
is simple but not precise.  For example a person born on 29 -May-1992 would be 17 on 29-May-2009, but this formula returns 16.
------------ The actual number of days in a year is 365.242199 so this one should work for just about all age calculation
```FLOOR(DATEDIFF(day,DOB,GETDATE())/365.242199)
```

theduke
Friday, September 17, 2010 1:32 AM
• Hi

This has helped me to a point, but can you tell me how i add in Months and Days to get an true age.

Ie with a DOB of 22 nov 73 i am 36y 10m 10d as of today

Tiny
• Edited by Friday, October 01, 2010 2:48 PM spelling
Friday, October 01, 2010 2:47 PM
• Hi

I used *100 trick to simplify the calcualation and still get correct result http://www.itcouple.co.uk/t-sql-age.aspx

Regards

Emil

Sunday, February 06, 2011 10:15 AM
• declare

@d1

datetime

set

@d1 = convert(datetime,'09/02/2001')

;

select

case when convert(datetime, datename(mm,@d1) + ',' + DATENAME(day,@d1)+ ' '+ DATENAME(year,Getdate

()))

< = getdate() then DATEDIFF(year,@d1, getdate())

else DATEDIFF(year,@d1 , getdate()) - 1

end age
Thursday, September 01, 2011 12:52 PM
• The following article also deals with age calculation:

http://www.sqlusa.com/bestpractices2005/employment/

Formulas using 365.25 are not accurate:

```DECLARE @Start DATE = '1900-09-01', @End DATE = '2011-09-01'
SELECT FLOOR(DATEDIFF(day,@Start,GETDATE())/365.25)
-- 110
-- It should be 111```

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Thursday, September 01, 2011 1:02 PM
• All of the "answers" do some form of rounding.  Either round to the whole year, using "floor", or dividing by an average like 365.242199
Yes my result rounds to 4 decimal places but that worked for what I needed.

-- Input to the function is two dates.
-- Output is the numeric number of years between the two dates in Decimal(7,4) format.
-- Output is always always a possitive number.
-- NOTE:Output does not handle if difference is greater than 999.9999

-- Logic is based on three steps.
-- 1) Is the difference less than 1 year (0.5000, 0.3333, 0.6667, ect.)
-- 2) Is the difference exactly a whole number of years (1,2,3, ect.)
-- 3) (Else)...The difference is years and some number of days. (1.5000, 2.3333, 7.6667, ect.)

CREATE

Function [dbo].[F_Get_Actual_Age](@pi_date1 datetime,@pi_date2 datetime)
RETURNS Numeric(7,4)

AS
BEGIN

Declare

@l_tmp_date
DATETIME
,@l_days1 DECIMAL(9,6)
,
@l_days2 DECIMAL(9,6)
,
@l_result DECIMAL(10,6)
,
@l_years DECIMAL(7,4

)

--Check to make sure there is a date for both inputs

IF @pi_date1 IS NOT NULL and @pi_date2 IS NOT NULL

BEGIN

IF @pi_date1 > @pi_date2 --Make sure the "older" date is in @pi_date1

BEGIN

SET @l_tmp_date = @pi_date2

SET @pi_date2 = @Pi_date1

SET @pi_date1 = @l_tmp_date

END

--Check #1 If date1 + 1 year is greater than date2, difference must be less than 1 year

BEGIN

--How many days between the two dates (numerator)

SET @l_days1 = DATEDIFF(dd,@pi_date1, @pi_date2)

--subtract 1 year from date2 and calculate days bewteen it and date2

--This is to get the denominator and accounts for leap year (365 or 366 days)

SET @l_years = @l_days1 / @l_days2 -- Do the math

END

ELSE

--Check #2 Are the dates an exact number of years apart.

--Calculate years bewteen date1 and date2, then add the years to date1, compare dates to see if exactly the same.

SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2) --AS Years, 'Exactly even Years' AS Msg

ELSE

BEGIN

--Check #3 The rest of the cases.

--Check if datediff, returning years, over or under states the years difference

SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2)

SET @l_years = @l_years -1

--use basicly same logic as in check #1

SET @l_years = @l_years + @l_days1 / @l_days2

--SELECT @l_years AS Years, 'Years Plus' AS Msg

END

END

ELSE

SET @l_years = 0 --If either date was null

RETURN

@l_Years

--Return the result as decimal(7,4)

END

Tuesday, November 29, 2011 3:15 PM
• As a shorter variant of this you can use the DatePart (dy...) function

So you can use this in your function:

DECLARE @age int

SET @age = DateDiff(yyyy,@in_DOB,@now) - case when DatePart(dy, @in_DOB )> DATEPART(dy,@now) THEN 1 ELSE 0 END

Which in essence says count the years and drop one if the days to the birthday event has not passed.

Tuesday, December 20, 2011 11:42 AM
• Thanks - this is exactly what i was searching for!
Wednesday, January 18, 2012 8:26 PM
• DECLARE @date1 as datetime
DECLARE @date2 as datetime
SET @date1  = '15-May-1975'
SET @date2  = GETDATE()

SELECT
CASE
WHEN DATEDIFF(MONTH, @date1, @date2) %  12 = 0 AND DATEPART(DAY,@date1) != DATEPART(DAY,@date2)
THEN CAST((DATEDIFF(MONTH, @date1, @date2) / 12) AS INT) - 1
ELSE
CAST((DATEDIFF(MONTH, @date1, @date2) / 12) AS INT)
END

--OR

SELECT
CAST((DATEDIFF(MONTH, @date1, @date2) / 12) AS INT)  -
(CASE WHEN DATEDIFF(MONTH, @date1, @date2) %  12 = 0 AND DATEPART(DAY,@date1) != DATEPART(DAY,@date2) THEN 1 ELSE 0 END )

• Edited by Friday, March 02, 2012 5:50 PM
Friday, March 02, 2012 5:49 PM
• Simple and always correct:

SET @Age = datediff(year, @DOB, @Now)
- case
when datepart(dayofyear, @DOB) > datepart(dayofyear, @Now)
then 1
else 0
end;  -- adjust for case where the birthday has not happened yet in current year

Wednesday, May 30, 2012 9:42 PM
• DECLARE @dbo   DATETIME = '3/1/2008'
DECLARE @pDate   DATETIME = '2/28/2011';
DECLARE @leap   BIT = 0

IF    (YEAR (@pDate) % 4 = 0 AND YEAR (@pDate) % 100 != 0)
OR YEAR (@pDate) % 400 = 0
BEGIN
SET @leap = 1
END

SELECT CASE
WHEN @leap = 1
THEN
datediff (yyyy, @dbo, @pDate)
- CASE
WHEN month (@pDate) > month (@dbo) THEN 0
WHEN month (@pDate) < month (@dbo) THEN 1
ELSE CASE WHEN day (@pDate) >= day (@dbo) THEN 0 ELSE 1 END
END
ELSE
CASE
WHEN month (@dbo) = 2 AND day (@dbo) = 29
THEN
datediff (yyyy, @dbo - 1, @pDate)
- CASE
WHEN month (@pDate) > month (@dbo - 1)
THEN
0
WHEN month (@pDate) < month (@dbo - 1)
THEN
1
ELSE
CASE
WHEN day (@pDate) >= day (@dbo - 1) THEN 0
ELSE 1
END
END
ELSE
datediff (yyyy, @dbo, @pDate)
- CASE
WHEN month (@pDate) > month (@dbo)
THEN
0
WHEN month (@pDate) < month (@dbo)
THEN
1
ELSE
CASE
WHEN day (@pDate) >= day (@dbo) THEN 0
ELSE 1
END
END
END
END

Thursday, June 07, 2012 4:41 PM
• Hey....

I followed your instructions...bt m getin this error...invalid object name 'dbo.fn_GetAge'...

is there any inbuilt function called getage or do we have to create it??

Tuesday, August 28, 2012 8:21 AM
• Sorry everyone, but most of the answers are wrong...

DATEDIFF(YEAR, '2012-12-31', '2013-01-13') = 1

FLOOR(DATEDIFF(day, '2012-03-01', '2013-03-01') / 365.25) = 0

DATEDIFF(MONTH, '2012-03-31', '2013-03-01') / 12 = 1

Ramesh Subramanian, the correct logic is:
CASE WHEN MONTH(@BirthDate) <= MONTH(@Today) AND DAY(@BirthDate) <= DAY(@Today)
THEN DATEDIFF(Year,@BirthDate,@Today)
ELSE DATEDIFF(Year,@BirthDate,@Today) - 1 END
But for a '2012-04-02' and '2013-05-01' the logic fails, because 04 < 05 (Month) but 02 > 01 (Day)

SQL Slave:
Explicit conversion from data type date to int is not allowed.
You need convert to smalldatetime (eg) before.
But on birthday date it isn't increment one year, and on leap Years have a lag of one day.

The fn_GetAge function of George Mastros is OK! But it's a function, and depending of data volume, it might custe on server.

DATEDIFF(yy,@DOB,@Today)-CASE WHEN DATEADD(yy,DATEDIFF(yy,@DOB,@Today),@DOB)>@Today THEN 1 ELSE 0 END
Also is OK, but a bit complex unnecessarily.

I sugest:

For DATE type:
DATEDIFF(Year, @DOB, @Date) - case when RIGHT(@DATE, 5) >= RIGHT(@DOB, 5) then 0 else 1 end

Or for all date types:
DATEDIFF(Year, @DOB, @Date) - case when RIGHT(CONVERT(DATE, @DATE), 5) >= RIGHT(CONVERT(DATE, @DOB), 5) then 0 else 1 end
Or:
DATEDIFF(Year, @DOB, @Date) - case when MONTH(@DATE) * 100 + DAY(@DATE) >= MONTH(@DOB) * 100 + DAY(@DOB) then 0 else 1 end

Thursday, February 21, 2013 12:51 PM
• Hi Alessandro,

Can you post a testable script for your solution? Thanks.

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Thursday, February 21, 2013 2:00 PM
• You can use the below query to get the months or days between two dates.

This should give you a good starting point.

DATEDIFF(mm, 0,@enddate - @startdate)

• Proposed as answer by Friday, January 24, 2014 10:31 PM
Friday, January 24, 2014 10:30 PM
• DATEDIFF(HOUR,DOB,GETDATE())/8766
Thursday, April 10, 2014 2:09 PM
• Your method does not work:

```DECLARE @Today DATE = CURRENT_TIMESTAMP;
SELECT @Today;  -- 2014-04-10

SELECT DATEDIFF(HOUR,'19670410',@Today)/8766;
-- 47

SELECT DATEDIFF(HOUR,'19680410',@Today)/8766;
-- 45```

Kalman Toth Database & OLAP Architect Free T-SQL Scripts
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Thursday, April 10, 2014 2:38 PM
• This works

CASE WHEN dateadd(year, datediff (year, @DateOfBirth, GETDATE()),@DateOfBirth) > GETDATE()
THEN datediff (year, @DateOfBirth, GETDATE()) - 1
ELSE datediff (year, @DateOfBirth, GETDATE())
END AS Age

Monday, October 13, 2014 3:26 PM
• Which is exactly what DaveB222 posted 5 years ago (and which is marked as an answer).
Monday, October 13, 2014 3:39 PM