15,849,328 members
See more:
How to calculate age from date of birth in sql
My table has column for DOB but I need Age how to calculate it in sql query
Posted
Updated 3-Feb-23 7:19am
Sumit Kanti 30-Sep-21 0:46am
My DOB is 19 March 2005

## Solution 2

SQL
`SELECT FLOOR(DATEDIFF(DAY, @BirthDate, @TargetDate) / 365.25)`

Ref: Calculating age based on date of birth in SQL[^]
or
SQL
`SELECT DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc`

Ref:How to calculate age (in years) based on Date of Birth and getDate()[^]

Refer:
sql age function[^]
calculate age on date of birth[^]
Date Calculations: calculate ages[^]

v2

## Solution 3

Hi ,

try this code block

SQL
```  DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days```

Pratika05 8-Jun-12 7:15am
thanks....good code

## Solution 24

Kelly's answer is almost correct. Very old thread, but this could help someone.

You can do this:
`select datediff(year,[bd], getdate()) - case when month([bd]) > month(getdate()) or (month([bd]) = month(getdate()) and day([bd]) > day(getdate())) then 1 else 0 end age from [table]`
Where table is your table name, and bd is the birth date field name in that table.

First you get the number of years from the birth date up to now.
`datediff(year,[bd], getdate())`
Then you need to check if the person already had this year's birthday, and if not, you need to subtract 1 from the total.

If the month is in the future
`month([bd]) > month(getdate())`
or if it is the birthday month but the day is in the future
`(month([bd]) = month(getdate()) and day([bd]) > day(getdate()))`

v2

## Solution 4

This is my solution for RAP, Report Object Pascal, as in ReportBuilder for Medisoft Report Professional

Delphi
```procedure AgeOnCalc(var Value: Variant);

var currentyear, currentmonth, currentday : integer;
patientyear, patientmonth, patientday : integer;

begin

decodedate(CurrentDate, currentyear, currentmonth, currentday);
decodedate(Patient['Date of Birth'], patientyear, patientmonth, patientday);
if (patientmonth > currentmonth) or ((patientmonth = currentmonth) and (patientday > currentday)) then
value := currentyear - patientyear - 1
else
value := currentyear - patientyear;

end;```

## Solution 1

To calculate the average age today, you could use `SELECT DATEDIFF(DAY, @Birth, GetDate()) / 365.25`.

However, the calculation could get more complex if you want the exact age in days or years.

v2

## Solution 5

Select ID,
convert(Float,convert(int,convert(varchar(10),GETDATE(),112))) as Todayday,
convert(float,convert(int,convert(varchar(10),convert(date,ltrim(rtrim(DOB)))),112))) as DOB,
(convert(Float,convert(int,convert(varchar(10),GETDATE(),112))) -
convert(float,convert(int,convert(varchar(10),convert(date,ltrim(rtrim(DOB)))),112))))/10000 as AgeFloat,
Convert(int,(convert(Float,convert(int,convert(varchar(10),GETDATE(),112))) -
convert(float,convert(int,convert(varchar(10),convert(date,ltrim(rtrim(DOB)))),112))))/10000) as Age
From DOBTable

Notes:
Format todays date and birthdate as YYYYMMDD and subtract today-DOB.
Convert that number to float and divide by 10000.
The integer of that result is age.
Convert from Float to integer does not round the number.
Age is exact every time.
I have added steps to show these conversions, but you only need the Age line.
Convert(int,(convert(Float,convert(int,convert(varchar(10),GETDATE(),112))) -
convert(float,convert(int,convert(varchar(10),convert(date,ltrim(rtrim(DOB)))),112))))/10000) as Age

Maciej Los 16-Oct-15 17:03pm
Do not post an answer to such of old question!

## Solution 16

Here's another way using SQL that will get you their specific age based on the current date and their date of birth.

Below is written for Oracle.

select dob,
case when to_char(cast(sysdate as date),'mm') || to_char(cast(sysdate as date),'dd') >=
to_char(dob,'mm') || to_char(dob,'dd')
then to_char(cast(sysdate as date),'yyyy') - to_char(dob,'yyyy')
else to_char(cast(sysdate as date),'yyyy') - to_char(dob,'yyyy') - 1
end age
from my_table

Here is a version of the same sql that works for Netezza.

select dob,
case when lpad(extract (months from current_date),2,'0') ||
lpad(extract (days from current_date),2,'0') >=
lpad(extract (months from dob),2,'0') ||
lpad(extract (days from dob),2,'0')
then extract (years from current_date) - extract (years from dob)
else extract (years from current_date) - extract (years from dob) - 1
end age
from my_table

For SQL Server :

select dob,
case when right('00' + cast(month(getdate()) as varchar(2)),2) + right('00' + cast(day(getdate()) as varchar(2)),2) >=
right('00' + cast(month(dob) as varchar(2)),2) + right('00' + cast(day(dob) as varchar(2)),2)
then year(getdate()) - year(dob)
else year(getdate()) - year(dob) - 1
end age
from my_table

## Solution 17

I know this is an older thread, but it will continue to be prevalent for years to come. To put everyone's minds at rest, I have tested a couple of the formulas presented on this thread to calculate age in years and both seem to be in sync. I personally I am leaning toward the first formula as it is short and elegant. If you want to put your mind at rest check it out for yourself:

SQL
```/*Create table to hold every single DOB since 1/1/1900*/
Create table dbo.Temp(DOB Date)
go

/*populate table with age*/

;with temp as(
select convert(date, '01/01/1900') As DOB
Union all
Select DateAdd(Day, 1, DOB)
from temp
Where DateAdd(Day, 1, DOB) <= Convert(Date, GetDate())
)
Insert into dbo.Temp(DOB)
select DOB
from temp
option(maxrecursion 0)

/*test two formulas for calculating age*/

;with test as(
Select DOB, Age1 = FLOOR(DATEDIFF(DAY, DOB , getdate()) / 365.25),
Age2 =
case when right('00' + cast(month(getdate()) as varchar(2)),2) + right('00' + cast(day(getdate()) as varchar(2)),2) >=
right('00' + cast(month(dob) as varchar(2)),2) + right('00' + cast(day(DOB) as varchar(2)),2)
then year(getdate()) - year(DOB)
else year(getdate()) - year(DOB) - 1
end
From dbo.Temp
)
select *
from test
where age1 <> age2```

## Solution 19

I know this question is old but I have an Inline Table Valued Function that calculates the age quite well.

The way it works is to calculate the difference between the `@StartTime` and `@EndTime` in months. Then it checks the day of `@StartTime` to see if it is greater than the day of `@EndTime`, if it is then subtract 1 from the month calculation because the person's birthday has not arrived yet. Otherwise, subtract 0.

By writing this as an Inline Table Valued Function rather than a Scalar function the query that it is used in will run very fast.

You can call it for a single calculation by:
SQL
`SELECT AgeInYears FROM dbo.CalculateAgeInYears('1980-07-01', GETDATE())`

Or you can use CROSS APPLY to calculate from a table:
SQL
```SELECT *
FROM #SampleData AS t
CROSS APPLY dbo.CalculateAgeInYears(t.DOB, GETDATE()) AS fn```

SQL
```CREATE FUNCTION [dbo].[CalculateAgeInYears]
(
@StartDate DATETIME,
@EndDATE DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT (DATEDIFF(MONTH, @StartDate, @EndDate) - CASE WHEN DATEPART(DAY, @StartDate) > DATEPART(DAY, @EndDate) THEN 1 ELSE 0 END) / 12 AS AgeInYears
)```

## Solution 20

Similar to Solution 5 but avoids conversions back and forth from text to numeric.

Assumes date of birth (dob) and 'as of' date (as_of_date) are in date format.
`floor((datepart (yy,as_of_date)+(datepart(mm,as_of_date)*.01)+(datepart(dd,as_of_date)*.0001))-(datepart (yy,dob)+(datepart(mm,dob)*.01)+(datepart(dd,dob)*.0001)))`

For dob = 7/12/1960 and as_of_date = 3/12/2020:

2020.0312 - 1960.0712 = 59.9600

The decimal portion is gibberish, but you are only looking for the floor() portion, which is 59.

## Solution 22

CREATE OR ALTER PROC AGE ( @dob datetime )
AS
DECLARE @year int ,@month INT,@day INT, @processyear datetime = @dob
select @year = datediff(yy,@processyear,GETDATE()) -
case
when (month(@DOB)>month(GETDATe())) OR MONTH(@DOB) = MONTH(GETDATE())AND DAY(@DOB)>DAY(GETDATE()) THEN 1 ELSE 0
END
SELECT @processyear = DATEADD(YEAR,@year,@processyear)

SELECT @MONTH = DATEDIFF(MONTH,@processyear,GETDATE()) -
case
when day(@DOB)>day(GETDATE())
then 1 else 0
end
PRINT @MONTH
select @processyear = DATEADD(month,@month,@processyear)

select @day =datediff(day,@processyear,getdate())
declare @age nvarchar(50)
set @age = cast(@year as nvarchar(4))+ 'years'+cast(@month as nvarchar(4)) + 'months' +cast(@day as nvarchar(4))+'days old'
SELECT @AGE

## Solution 23

SQL
```DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE), @DOB DATE = '1967-01-10', @Years INT = 0, @Months INT = 0, @Days INT = 0
SET @Years = DATEDIFF(DAY,@DOB,@CurrentDate)/365
SET @Months = DATEDIFF(MONTH,DATEADD(YEAR,DATEDIFF(DAY,@DOB,@CurrentDate)/365,@DOB),@CurrentDate)
SELECT CONCAT (@Years, ' Years, ', @Months, ' Months and ', @Days, ' Days') AS DateOfBirth```

This give us exact Years, Months and Days. And we can create function out of this.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard MacCutchan 60 Graeme_Grant 40 Dave Kreskowiak 20 Maxim Kartavenkov 20 OriginalGriff 20
 OriginalGriff 2,253 Richard Deeming 1,040 Dave Kreskowiak 706 Graeme_Grant 689 Richard MacCutchan 620

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900