Subject: Re: integer division
Newsgroups: comp.databases.oracle.server
References: <5p9chn$j4r@pelican.cs.ucla.edu>
Reply-To: akaplan@interaccess.com
Distribution: 

Daren Lee (dalee@pelican.cs.ucla.edu) wrote:
: hi
: does anyone know how to force integer division?
: 
: i'm subtracting 2 DATE types to get the number of
: days between them and then i need to divide by 7.
: e.g:
: to_char(date1,'j')-to_char(date2,'j')/7
: 
: thanks

Daren,

It looks like you are trying to find the number of weeks between two dates.
Oracle has a built-in date function for MONTHS_BETWEEN, but unfortunately none
for WEEKS_BETWEEN.

To do this, you can:

SELECT (TO_CHAR(date1,'j')-TO_CHAR(date2,'j'))/7 FROM dual;

This will return a number such as .285714286. If you want this to be an integer,
it is up to you to decide to round up/down. The TRUNC and ROUND functions will
suffice.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 70+ technical tips, visit my Web Page:                    <->
<->                                                               <->
<->              www.arikaplan.com                                <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page ">Back to Ari Kaplan's Home Page