Click here to Skip to main content
12,451,093 members (51,948 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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
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 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 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 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 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

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
Web02 | 2.8.160826.1 | Last Updated 16 Oct 2015
Copyright © CodeProject, 1999-2016
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