0

How to calculate age using DOB in oracle sql? Column name: GET_DOB Table name: J , DOB Date format: 8/10/2022

Jt R
  • 1
  • 1
  • Does this answers your question https://stackoverflow.com/questions/3015431/oracle-age-calculation-from-date-of-birth-and-today#:~:text=SELECT%20TRUNC((SYSDATE%20%2D%20TO_DATE,and%20straight%20to%20the%20point. – Suresh Mangs Aug 12 '22 at 15:04
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Aug 13 '22 at 04:12

1 Answers1

0

Assuming your DOB column is a date you could do the following:

SELECT a.*, TRUNC(MONTHS_BETWEEN(sysdate, dob) / 12) age
FROM   your_table a

otherwise you need to cast string to date:

SELECT a.*, TRUNC(MONTHS_BETWEEN(sysdate, TO_DATE(dob, 'DD/MM/YYYY')) / 12) age
FROM   your_table a

In case you want to have age as a column (bear in mind that age changes with time):

CREATE VIEW your_view
AS
  SELECT a.*,
         TRUNC(MONTHS_BETWEEN(sysdate, dob) / 12) age
  FROM   your_table a;

Use this SQL fiddle for reference: http://sqlfiddle.com/#!4/c8e1b/1

Dezzley
  • 1,463
  • 1
  • 13
  • 19
  • Will this result be in years or months? – Jt R Aug 12 '22 at 15:24
  • @JtR in full years – Dezzley Aug 12 '22 at 15:30
  • It didn’t work as it is in another select statement. Would the syntax be different for making it a sub query? – Jt R Aug 12 '22 at 15:34
  • The overall logic will be the same. Could you elaborate on what exactly you want to achieve please? – Dezzley Aug 12 '22 at 15:44
  • Yes I want this to show up as a column along with multiple other colums in the result. For example: select abc, k. Name, k. Gender, k. DOB, k. Age, k. City – Jt R Aug 12 '22 at 15:46
  • Since age always changes I would recommend you create a view with this calculated `AGE` column. I have just edited my post so you can see it there. – Dezzley Aug 12 '22 at 15:50
  • Since this is in another query, should I start with a bracket” ( “ ? – Jt R Aug 12 '22 at 15:54
  • Once you create a view you can explicitly query it as you do with tables: `select * from your_view`; – Dezzley Aug 12 '22 at 15:55
  • So this is how the whole thing will look like: select abc, k. Name, k. Gender, k. DOB, k. Age, CREATE VIEW your_view AS SELECT a.*, TRUNC(MONTHS_BETWEEN(sysdate, dob) / 12) age FROM your_table a; k. City ? – Jt R Aug 12 '22 at 16:01
  • Not exactly. See the SQLFiddle I attached to my answer. `View` is a virtual table based on the result-set of an SQL statement. It would help you have `age` calculated on the fly each time you query the view. I also edited my answer to include the rest of the columns into the result set. – Dezzley Aug 12 '22 at 16:08
  • Can you write this in my query that I wrote? select abc, k. Name, k. Gender, k. DOB, k. Age, k. City – Jt R Aug 12 '22 at 16:16
  • select k.City, k.Name, k.Gender, k.DOB, TRUNC(MONTHS_BETWEEN(sysdate, k.DOB) / 12) Age FROM your_table k; – Dezzley Aug 12 '22 at 16:19
  • So I am not creating a view anymore? – Jt R Aug 12 '22 at 16:22
  • In case you decide to create a view: `create view your_view as select k.City, k.Name, k.Gender, k.DOB, TRUNC(MONTHS_BETWEEN(sysdate, k.DOB) / 12) Age from your_table k;` Then query your view: `select * from your_view;` – Dezzley Aug 12 '22 at 16:24