I HAVE A DOUBT REGARDING CALCULATION OF AGE.
WHY DO WE DIVIDE BY 365 WHILE FINDING AGE.IN MY OPINION IT HAS TO BE ONLY SYSDATE -DATEOFBIRTH
Calculating age is not as trivial as it seems, when you look at leap years and things like that. Here's some examples - you'll probably want to choose the last one
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t
2 ( birth date,
3 now date,
4 expected int );
Table created.
SQL>
SQL>
SQL> insert into t values ( date '2000-05-12',date '2016-03-30',15 );
1 row created.
SQL> insert into t values ( date '2000-05-12',date '2016-05-30',16 );
1 row created.
SQL> insert into t values ( date '2000-05-12',date '2016-05-12',16 );
1 row created.
SQL> insert into t values ( date '2000-05-12',date '2076-06-12',76 );
1 row created.
SQL> insert into t values ( date '2000-02-29',date '2013-03-01',13 );
1 row created.
SQL> insert into t values ( date '2000-02-29',date '2016-02-29',16 );
1 row created.
SQL> insert into t values ( date '2000-02-29',date '2017-02-28',16 );
1 row created.
SQL> insert into t values ( date '2000-02-29',date '2087-02-28',86 );
1 row created.
SQL> insert into t values ( date '2000-01-01',date '2087-12-31',87 );
1 row created.
SQL>
SQL> select
2 x.*,
3 case when expected = c1 then 'OK' else 'ERROR' end tag
4 from
5 (
6 select
7 t.*,
8 trunc((now-birth)/365) c1
9 from t
10 ) x;
BIRTH NOW EXPECTED C1 TAG
--------- --------- ---------- ---------- -----
12-MAY-00 30-MAR-16 15 15 OK
12-MAY-00 30-MAY-16 16 16 OK
12-MAY-00 12-MAY-16 16 16 OK
12-MAY-00 12-JUN-76 76 76 OK
29-FEB-00 01-MAR-13 13 13 OK
29-FEB-00 29-FEB-16 16 16 OK
29-FEB-00 28-FEB-17 16 17 ERROR
29-FEB-00 28-FEB-87 86 87 ERROR
01-JAN-00 31-DEC-87 87 88 ERROR
9 rows selected.
SQL>
SQL> select
2 x.*,
3 case when expected = c1 then 'OK' else 'ERROR' end tag
4 from
5 (
6 select
7 t.*,
8 trunc(months_between(now,birth)/12) c1
9 from t
10 ) x;
BIRTH NOW EXPECTED C1 TAG
--------- --------- ---------- ---------- -----
12-MAY-00 30-MAR-16 15 15 OK
12-MAY-00 30-MAY-16 16 16 OK
12-MAY-00 12-MAY-16 16 16 OK
12-MAY-00 12-JUN-76 76 76 OK
29-FEB-00 01-MAR-13 13 13 OK
29-FEB-00 29-FEB-16 16 16 OK
29-FEB-00 28-FEB-17 16 17 ERROR
29-FEB-00 28-FEB-87 86 87 ERROR
01-JAN-00 31-DEC-87 87 87 OK
9 rows selected.
SQL>
SQL>
SQL> select
2 x.*,
3 case when expected = c1 then 'OK' else 'ERROR' end tag
4 from
5 (
6 select
7 t.*,
8 trunc((to_number(to_char(now,'YYYYMMDD'))-
9 to_number(to_char(birth,'YYYYMMDD')))/10000) c1
10 from t
11 ) x;
BIRTH NOW EXPECTED C1 TAG
--------- --------- ---------- ---------- -----
12-MAY-00 30-MAR-16 15 15 OK
12-MAY-00 30-MAY-16 16 16 OK
12-MAY-00 12-MAY-16 16 16 OK
12-MAY-00 12-JUN-76 76 76 OK
29-FEB-00 01-MAR-13 13 13 OK
29-FEB-00 29-FEB-16 16 16 OK
29-FEB-00 28-FEB-17 16 16 OK
29-FEB-00 28-FEB-87 86 86 OK
01-JAN-00 31-DEC-87 87 87 OK
9 rows selected.
SQL>
SQL>