Click here to Skip to main content
11,634,827 members (65,550 online)
Rate this: bad
good
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 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 at 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

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

  Print Answers RSS
0 OriginalGriff 8,736
1 Sergey Alexandrovich Kryukov 8,419
2 Mika Wendelius 6,845
3 F-ES Sitecore 2,354
4 Suvendu Shekhar Giri 2,205


Advertise | Privacy | Mobile
Web04 | 2.8.150728.1 | Last Updated 24 Feb 2015
Copyright © CodeProject, 1999-2015
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