1. megoldás:
SQL> select trunc(months_between(sysdate,dob)/12) year,
2 trunc(mod(months_between(sysdate,dob),12)) month,
3 trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
4 from (Select to_date('15122000','DDMMYYYY') dob from dual);
YEAR MONTH DAY
---------- ---------- ----------
9 5 26
SQL>
2. megoldás:
SELECT TRUNC(
MONTHS_BETWEEN(
SYSDATE,
CASE TO_CHAR(birthdate,'MMDD')
WHEN '0229' -- born on February 29
THEN CASE TO_CHAR(TRUNC(SYSDATE,'YYYY') + 59,'MMDD')
WHEN '0229'-- current year is leap year
THEN birthdate
ELSE birthdate - 1 -- if person was born on February 29 and current year is not a leap year
END -- we will consider person was born on February 28
ELSE birthdate
END
) / 12
) age
FROM tbl
3. megoldás:
SELECT FLOOR((SYSDATE - (ADD_MONTHS(SYSDATE,-22)+1))/365) YEARS,
MOD( (SYSDATE - (ADD_MONTHS(SYSDATE,-22)+1)),365) DAYS
FROM DUAL
4. megoldás:
select
trunc((to_number(to_char(sysdate,'yyyymmdd'))
-to_number(to_char(date '2002-12-07','yyyymmdd'))
)/10000) as "age of this thread"
from dual;