13,000,829 members (56,012 online)
Rate this:
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 7-Jun-12 20:55pm
Updated 17-Mar-17 11:02am

Rate this:

## Solution 2

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

Ref: Calculating age based on date of birth in SQL[^]
or
`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
Rate this:

## Solution 3

Hi ,

try this code block

```
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
```
Deepu05 8-Jun-12 7:15am

thanks....good code
Rate this:

## Solution 4

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

```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;```
Rate this:

## 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
Rate this:

## 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!
Rate this:

## Solution 7

I think you'll like this one. And it's more accurate than / 365.24.

```DECLARE
@BirthDate date = '19801214',
@TargetDate date = GetDate();

SELECT (
Convert(int, Format(@TargetDate, 'yyyyMMdd'))
- Convert(int, Format(@BirthDate, 'yyyyMMdd'))
) / 10000;```
CHill60 17-Mar-17 19:08pm

This is a seriously old post and there are sooo many solutions out there. Please don't resurrect ancient, pointless questions by posting largely redundant solutions
ppolymorphe 17-Mar-17 19:10pm

5 years too late

Top Experts
Last 24hrsThis month
 RickZeeland 94 ppolymorphe 65 OriginalGriff 60 Kornfeld Eliyahu Peter 50 Dave Kreskowiak 50
 OriginalGriff 4,607 ppolymorphe 2,357 RickZeeland 2,211 Kornfeld Eliyahu Peter 1,993 Richard Deeming 1,633