Answered by:
Calculating age using sql server function

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?
 Edited by Kalman TothModerator Thursday, April 10, 2014 2:40 PM spelling
Question
Answers

is simple but not precise. For example a person born on 29 May1992 would be 17 on 29May2009, but this formula returns 16.
FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25)
FLOOR(DATEDIFF(day,DOB,GETDATE())/365.242199)
theduke Proposed as answer by Naomi NModerator Friday, September 17, 2010 1:52 AM
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:23 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

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 = '20040229'
declare @CompareDate datetime Set @CompareDate = '20050228'  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
· Add the difference in years to DOB (using DATEADD)
· 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. Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:24 PM

Hi
I used *100 trick to simplify the calcualation and still get correct result http://www.itcouple.co.uk/tsqlage.aspx
Regards
Emil
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:46 PM

declare
@d1
datetime
set
@d1 = convert(datetime,'09/02/2001')
;
select
case when convert(datetime, datename(mm,@d1) + ',' + DATENAME(day,@d1)+ ' '+ DATENAME(year,Getdate
()))
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:23 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.

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



Uh...it's way easier than this. Using the "/" function with integer data types always rounds down (or "floors") the result:
Code Snippetalter 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 Snippetselect *, datediff(mm, DOB, getdate()) / 12 as [Age]
from <MyTable>

rusag2 wrote: Uh...it's way easier than this. Using the "/" function with integer data types always rounds down (or "floors") the result:
Code Snippetalter 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 Snippetselect *, datediff(mm, DOB, getdate()) / 12 as [Age]
from <MyTable>
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 Snippetselect datediff(mm, '20071122', getdate()) / 121
select FLOOR(DATEDIFF(day, '20071122', getDate()) / 365.25)0
 Proposed as answer by Naomi NModerator Friday, September 17, 2010 1:51 AM
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:26 PM
 Unmarked as answer by Kalman TothModerator Thursday, September 01, 2011 1:28 PM
 Unproposed as answer by Kalman TothModerator Thursday, September 01, 2011 1:42 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. yyyymmdd hh:mms.nnn) 
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 
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
(datediff(yy,@DOB,getdate()))(datepart(yy,(convert(datetime,convert(int,dateadd(yy,datediff(yy,@DOB,getdate()),@DOB))convert(int,dateadd(yy,1,getdate())))))1900)
/*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*/ 

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 May1992 would be 17 on 29May2009, but this formula returns 16. 
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 = '20040229'
declare @CompareDate datetime Set @CompareDate = '20050228'  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
· Add the difference in years to DOB (using DATEADD)
· 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. Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:24 PM

is simple but not precise. For example a person born on 29 May1992 would be 17 on 29May2009, but this formula returns 16.
FLOOR(DATEDIFF(day, DOB, getDate()) / 365.25)
FLOOR(DATEDIFF(day,DOB,GETDATE())/365.242199)
theduke Proposed as answer by Naomi NModerator Friday, September 17, 2010 1:52 AM
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:23 PM

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 Tinytoontown Friday, October 01, 2010 2:48 PM spelling

Hi
I used *100 trick to simplify the calcualation and still get correct result http://www.itcouple.co.uk/tsqlage.aspx
Regards
Emil
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:46 PM

declare
@d1
datetime
set
@d1 = convert(datetime,'09/02/2001')
;
select
case when convert(datetime, datename(mm,@d1) + ',' + DATENAME(day,@d1)+ ' '+ DATENAME(year,Getdate
()))
 Marked as answer by Kalman TothModerator Thursday, September 01, 2011 1:23 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 = '19000901', @End DATE = '20110901' SELECT FLOOR(DATEDIFF(day,@Start,GETDATE())/365.25)  110  It should be 111
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70461 & Job Interview: Programming SQL Server 2012
 Edited by Kalman TothModerator Thursday, February 21, 2013 1:45 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
BEGINDeclare
@l_tmp_date DATETIME
,@l_days1 DECIMAL(9,6)
,@l_days2 DECIMAL(9,6)
,@l_result DECIMAL(10,6)
,@l_years DECIMAL(7,4)
RETURN
@l_Years
Return the result as decimal(7,4)
END

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.


DECLARE @date1 as datetime
DECLARE @date2 as datetime
SET @date1 = '15May1975'
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
ORSELECT
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 Singanan K Friday, March 02, 2012 5:50 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
 Proposed as answer by Russell Shilling Wednesday, May 30, 2012 9:42 PM

DECLARE @dbo DATETIME = '3/1/2008'
DECLARE @pDate DATETIME = '2/28/2011';
DECLARE @leap BIT = 0IF (YEAR (@pDate) % 4 = 0 AND YEAR (@pDate) % 100 != 0)
OR YEAR (@pDate) % 400 = 0
BEGIN
SET @leap = 1
ENDSELECT 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 

Sorry everyone, but most of the answers are wrong...
DATEDIFF(YEAR, '20121231', '20130113') = 1
FLOOR(DATEDIFF(day, '20120301', '20130301') / 365.25) = 0
DATEDIFF(MONTH, '20120331', '20130301') / 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 '20120402' and '20130501' the logic fails, because 04 < 05 (Month) but 02 > 01 (Day)
SQL Slave:
convert(int,dateadd(yy,1,getdate()))
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
 Edited by Alessandro  AlSantsu Thursday, February 21, 2013 12:51 PM
 Proposed as answer by csteve777 Wednesday, March 20, 2013 8:07 PM

Hi Alessandro,
Can you post a testable script for your solution? Thanks.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70461 & Job Interview: Programming SQL Server 2012 Proposed as answer by Mitchell McClure Thursday, April 10, 2014 2:08 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 Blackwell SQL Friday, January 24, 2014 10:31 PM

DATEDIFF(HOUR,DOB,GETDATE())/8766
 Proposed as answer by Mitchell McClure Thursday, April 10, 2014 2:09 PM
 Unproposed as answer by Kalman TothModerator Thursday, April 10, 2014 2:39 PM

Your method does not work:
DECLARE @Today DATE = CURRENT_TIMESTAMP; SELECT @Today;  20140410 SELECT DATEDIFF(HOUR,'19670410',@Today)/8766;  47 SELECT DATEDIFF(HOUR,'19680410',@Today)/8766;  45
Kalman Toth Database & OLAP Architect Free TSQL Scripts
New Book / Kindle: Exam 70461 Bootcamp: Querying Microsoft SQL Server 2012 
