How old are you?

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age

trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000)

it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) to be 18 if I were born Feb 28th, 1990.

6 thoughts on “How old are you?”

  1. oh greate!!

    def birthdate = to_date(’2000/02/29′,’yyyy/mm/dd’)
    def sys_date = to_date(’2003/02/28′,’yyyy/mm/dd’)

    select
    trunc((to_char(&sys_date,’YYYYMMDD’)-to_char(&birthdate,’YYYYMMDD’))/10000) as age from dual;

    def sys_date = to_date(’2003/03/01′,’yyyy/mm/dd’)

    select
    trunc((to_char(&sys_date,’YYYYMMDD’)-to_char(&birthdate,’YYYYMMDD’))/10000) as age from dual;

  2. Inaccuate:

    SELECT TRUNC((’20090731′-’20040731′)/10000) from dual returns 5, right
    SELECT TRUNC((’20090731′-’20040730′)/10000) from dual returns 5, wrong

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>