Re: integer division

Daren Lee (dalee_at_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:                    <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->