CODEWITHSUNDEEP
plsql
user1683987
user1683987

Reputation: 533

PL/SQL: How can I calculate the AGE knowing a column called Birth_Date?

I have a table consisting of one column (BIRTH_DATE). How could I use sysdate to subtract from all the rows of my BIRTH_DATE and GROUP BY them together?

to be more clear:

Here is what I have fetched, now I would like to add an AGE Column to the below Table for all rows

ID     Birth_Date
___    ___________
1       02-JAN-63
2       23-OCT-31
3       30-DEC-35
4       06-MAY-83

Thanks in advance!

Upvotes: 3

Views: 14672

Answers (4)

Serum
Serum

Reputation: 305

Its been a long time. Hope this helps.

declare
   patAge int;
   patName patient.fname%type;
   cursor agePat is 
      SELECT fname, MONTHS_BETWEEN(sysdate, dob) / 12 age FROM patient;
begin
   open agePat;
   loop
      fetch agePat into patName, patAge;
      exit when agePat%notfound;
      dbms_output.put_line('Patient Age = ' || patAge || ' || Patient Name = ' || patName);
   end loop;
   close agePat;
end;
/

patAge here is to store the age value and patName to store the value of patient's name in the table.

I turned @JeffreyKemp solution into pl sql code.

Upvotes: 3

Francisco Coenda
Francisco Coenda

Reputation: 110

SELECT TRUNC(months_between(SYSDATE, birthDate) / 12) AS age

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

To get a person's age according to the usual criterion (i.e. according to the number of calendar years that have passed since their birth), taking into account leap years, you can use the MONTHS_BETWEEN operator:

SELECT id, MONTHS_BETWEEN(sysdate, birth_date) / 12 age FROM my_table;

Upvotes: 7

WoMo
WoMo

Reputation: 7246

Simply subtract birth_date from sysdate:

select id, (sysdate - birth_date) / 365 age from my_table;

Subtracting dates results in the number of days, so dividing by 365 will give you decimal years.

Upvotes: 5

Related Questions