Click here to Skip to main content
13,000,829 members (56,012 online)
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 7-Jun-12 20:55pm
Updated 17-Mar-17 11:02am
Rate this: bad
 
good
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[^]
  Permalink  
v2
Rate this: bad
 
good
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
 
  Permalink  
Comments
Deepu05 8-Jun-12 7:15am
   
thanks....good code
Rate this: bad
 
good
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;
  Permalink  
Rate this: bad
 
good
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.
  Permalink  
v2
Rate this: bad
 
good
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
  Permalink  
Comments
Maciej Los 16-Oct-15 17:03pm
   
Do not post an answer to such of old question!
Rate this: bad
 
good
Please Sign up or sign in to vote.

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;
  Permalink  
Comments
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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170624.1 | Last Updated 17 Mar 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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