Re: SQL Question:Age From Birthdate

From: Kimmy Chan <kimmy_chan_at_vickers.com.hk>
Date: Wed, 27 May 1998 11:27:29 +0800
Message-ID: <356B8820.60CAE0C1@vickers.com.hk>

Hi,

    I think your suggested SQL is only a simple and quick estimation as it is not exactly 365 days in a year. Due to the existence of leap year, some exceptions exist :

Case 1

---

Birthday is 01-Feb-1996
then age 4 will start from 31-Jan-2000 instead of 01-Feb-2000 according to your formula.
Actually, this problem will amplify every 4 years (i.e. 2 days before actual birthday for age 8, 3 days for age 12 etc)

Case 2

---

Birthday is 29-Feb-1996 (1996 is a leap year) then age 1 will start from 28-Feb-1997 (1997 is not a leap year) or 01-Mar-1997?
According to your formula, it will start from 01-Mar-1997. I don't know which one is correct according to the tradition/custom. Any suggestion or comment?

If I don't consider case 2, I have the following proposed SQL

select
/* get estimated age first */
to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') - /* adjust the age according to the month and day */ decode(sign(to_char(sysdate,'mm')-

            to_char(birthday,'mm')),
       -1,1, /* earlier month */
       +1,0, /* later month */
       /* same month, then determine by the day part */
       0,decode(sign(to_char(sysdate,'dd')-
                     to_char(birthday,'dd')),
                -1,1, /* earlier day */
                0 /* later or same day */

))
from emp;

Regards,

Kimmy.

Stanley Fung wrote:

> select trunc(trunc(sysdate-birthday)/365) from emp;

>

> Michael Wallach wrote:
> >
> > Does anyone know how using SQL I can determine someone's age based on
> > their birthdate?
> >
> > Mike

>

> --
> ------------------------------------------------------------
> Stanley Fung | Software Analyst
> Mobile Data Solutions Inc. | Direct Line: (604)207-6290
> Suite 135 | Tel: (604)270-9939
> 10551 Shellbridge Way, | Fax: (604)270-1310
> Richmond, B.C. | E-Mail: sfung_at_mdsi.bc.ca
> Canada V6X-2W9 | http://www.mdsi-advantex.com
> ------------------------------------------------------------
Received on Tue May 26 1998 - 22:27:29 CDT