From: James Hanway <hanwayj@NOSPAM.dfo-mpo.gc.ca>
Subject: Re: Date function question
Date: 2000/03/03
Message-ID: <38BFFF36.20491905@NOSPAM.dfo-mpo.gc.ca>#1/1
Content-Transfer-Encoding: 7bit
References: <89op0a$k27$1@nntp3.atl.mindspring.net> <38BFEBFF.499D4DE9@NOSPAM.dfo-mpo.gc.ca> <89orcc$iv6$1@nntp3.atl.mindspring.net>
X-Accept-Language: en
Content-Type: text/plain; charset=us-ascii
X-Trace: sapphire.mtt.net 952107040 142.176.61.253 (Fri, 03 Mar 2000 14:10:40 AST)
Organization: Business Internet
MIME-Version: 1.0
NNTP-Posting-Date: Fri, 03 Mar 2000 14:10:40 AST
Newsgroups: comp.databases.oracle.server


Or, if you just wanted the number of days between two dates:

SQL> select to_char(sysdate, 'J') -
to_char(to_date('28-APR-1972','DD-MON-YYYY'), 'J') from dual;

TO_CHAR(SYSDATE,'J')-TO_CHAR(TO_DATE('28-APR-1972','DD-MON-YYYY'),'J')
----------------------------------------------------------------------
                                                                 10171


The "J" date mask returns Julian days - days since December 31, 4713 BC


Abey Joseph wrote:
> 
> James,
>             MONTHS_BETWEEN is more accurate than days/365.  I was hoping to
> for something that'll show me , 27 years and 9 months! :-)
> Thanks for your help!
> 
> "James Hanway" <hanwayj@NOSPAM.dfo-mpo.gc.ca> wrote in message
> news:38BFEBFF.499D4DE9@NOSPAM.dfo-mpo.gc.ca...
> > Is the function MONTHS_BETWEEN of any use to you?
> >
> > SQL> select months_between(sysdate, to_date('28-APR-1972',
> > 'DD-MON-YYYY'))/12 from dual;
> >
> > MONTHS_BETWEEN(SYSDATE,TO_DATE('28-APR-1972','DD-MON-YYYY'))/12
> > ---------------------------------------------------------------
> >                                                      27.8508882
> >
> >
> > James.
> >
> >
> > Abey Joseph wrote:
> > >
> > > I am writing a report that shows a bunch of customer data.  I need to
> > > extract the exact age of the customer.  I am not sure if there is an
 Oracle
> > > function that will give me the exact age.  I subtracted the
 date_of_birth
> > > from the sysdate and I get the number of days in between.  How can I
 then
> > > translate that to years and months? I can divide the days by 365, but
 the
> > > margin of error gets bigger, the older the customer is.  Any help is
> > > appreciated.
> > >
> > > Abey Joseph
> > > abeyj@netzero.net