Click here to Skip to main content
14,576,808 members
Rate this:
Please Sign up or sign in to vote.
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 11-May-20 5:41am
Rate this:
Please Sign up or sign in to vote.

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:
Please Sign up or sign in to vote.

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
   
Comments
Pratika05 8-Jun-12 7:15am
   
thanks....good code
Rate this:
Please Sign up or sign in to vote.

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:
Please Sign up or sign in to vote.

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:
Please Sign up or sign in to vote.

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
   
Comments
Maciej Los 16-Oct-15 17:03pm
   
Do not post an answer to such of old question!
Rate this:
Please Sign up or sign in to vote.

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
   
Rate this:
Please Sign up or sign in to vote.

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:

/*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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100