Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, dodo.

Asked: September 28, 2016 - 6:47 pm UTC

Last updated: September 30, 2016 - 8:51 am UTC

Version: ORACLE 2016

Viewed 10K+ times! This question is

You Asked

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

and Connor said...

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>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Divide by 10000?

A reader, September 29, 2016 - 12:15 pm UTC

Want to understand why are you dividing by 10000
How did you get this number 10000
Connor McDonald
September 29, 2016 - 1:22 pm UTC

We are converting the dates to YYYYxxxx

So the years are in effect, multiples of 10,000 (because the 'xxxx' is capped at 9999). The 'xxxx' (ie months/days) becomes the means via which we decide on boundary points, that is, when comparing YYYYxxxx to ZZZZwwww, we only need to care about whether the 'xxxx' or greater or less than 'wwww' to decide whether a year has "ticked over". THis way, we dont need to worry about leap years etc etc

I didnt "invent" this - I saw it many years ago somewhere, so apologies for not doing a proper acknowledgement to whoever first used it.

Why not just go after it by the Year

David Pulliam, September 29, 2016 - 1:47 pm UTC

In most cases, at least from what I have seen from a practical approach, age can be easily defined as years only. Generally at least in my field of work we would approach this by:
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(TO_CHAR(BIRTHDATE,'YYYY')) from people;


Or something along that line of flare and it totally removes the leap year considerations. Again, this only works if you do not care about months and days related to age, just years.
Connor McDonald
September 30, 2016 - 8:49 am UTC

This is a reasonable approximation, but still...an approximation.

Another option....

J. Laurindo Chiappa, September 29, 2016 - 5:26 pm UTC

If months and days are required, maybe we could use this version :

SYSTEM:@XE:SQL> select
2 birth,
3 now,
4 floor(months_between(NOW,BIRTH)/12) AS years,
5 mod(trunc( months_between(NOW,BIRTH) ), 12) AS months,
6 NOW - add_months(BIRTH, trunc(months_between(NOW,BIRTH)) ) AS days,
7 expected
8* from T;


BIRTH NOW YEARS MONTHS DAYS EXPECTED
---------- ---------- ---------- ---------- ---------- ----------
12/05/2000 30/03/2016 15 10 18 15
12/05/2000 30/05/2016 16 0 18 16
12/05/2000 12/05/2016 16 0 0 16
12/05/2000 12/06/2076 76 1 0 76
29/02/2000 01/03/2013 13 0 1 13
29/02/2000 29/02/2016 16 0 0 16
29/02/2000 28/02/2017 17 0 0 16
29/02/2000 28/02/2087 87 0 0 86
01/01/2000 31/12/2087 87 11 30 87

9 rows selected.

SYSTEM:@XE:SQL>

==> all the occurences for BIRTH in the provided data were in year 2000 so we could not test for leap/non-leap years, century changes and alike but I think that this logic will handle these things - of course, more testing is recommended before use for sure...

Regards,

J. Laurindo Chiappa
Chris Saxon
September 30, 2016 - 8:51 am UTC

nice input