Search code examples
sqldatabaseoracle-sqldevelopersystimestamp

How do i get an age according to the current system time in NLS_DATE_FORMAT? (Oracle SQL)

I have a table called person, how do i get a person age according to the current system time in NLS_DATE_FORMAT?

ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';

CREATE TABLE person (
  person_id      NUMBER (9),
  birthday       DATE CONSTRAINT nn_person_birthday NOT NULL,
  ...
);

Note: I just want the exact age, not months neither days. Example Age: 43

Note2: I insert birthday like this in my table: TO_DATE('17.05.2003','DD.MM.YYYY')

Solution

  • The date format doesn't matter. I would suggest:

    floor(months_between(sysdate, p.birthday) / 12)