Skip to Main Content
  • Questions

  • Date Arithmetic: Remainder vs. Modulus

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Steven.

Asked: July 09, 2007 - 4:01 pm UTC

Last updated: July 10, 2007 - 8:09 pm UTC

Version: 10g Release 2 (10.2)

Viewed 10K+ times! This question is

You Asked

I am working on formating a report of overdue items. One of the requirements is to list the elapsed time between the due date and the current date, expressed in terms of years and days. I have ran across some curious findings in the results from the REMAINDER function which has led me to use the MOD function instead. Consider the following:

DECLARE
d1 DATE;
d2 DATE;
d3 DATE;
years NUMBER(38,12);
years2 NUMBER(38,12);
v_raw NUMBER(38,12);
v_raw2 NUMBER(38,12);
v_remainder NUMBER(38,12);
v_mod NUMBER(38,12);
v_mod2 NUMBER(38,12);
v_rem2 NUMBER(38,12);
BEGIN
d1 := to_date( '09-JUL-2007', 'DD-MON-YYYY' );--SYSDATE;
d2 := to_date( '24-JUL-1995', 'DD-MON-YYYY' );
d3 := to_date( '24-JUN-1995', 'DD-MON-YYYY' );

years := TRUNC( ( d1 - d2 )/365 );
years2 := TRUNC( ( d1 - d2 )/365 );
v_raw := d1 - d2;
v_raw2 := d1 - d3;
v_remainder := TRUNC( REMAINDER( d1 - d2, 365) );
v_rem2 := TRUNC( REMAINDER( d1 - d3, 365) );
v_mod := TRUNC( MOD( d1 - d2, 365 ) );
v_mod2 := TRUNC( MOD( d1 - d3, 365 ) );

dbms_output.put_line( 'D1 is '||to_char( d1, 'DD-MON-YYYY' )||'.');
dbms_output.put_line( 'D2 is '||to_char( d2, 'DD-MON-YYYY' )||'.');
dbms_output.put_line( 'D3 is '||to_char( d3, 'DD-MON-YYYY' )||'.');
dbms_output.put_line( 'Raw difference between d1 and d2 is '||v_raw||'.');
dbms_output.put_line( 'Number whole years difference between d1 and d2 is '||years||'.');
dbms_output.put_line( 'Number whole days difference, using the remainder function, between d1 and d2 is '||v_remainder||'.');
dbms_output.put_line( 'Number whole days difference, using the modulus function, between d1 and d2 is '||v_mod||'.');
dbms_output.put_line( 'Raw difference between d1 and d3 is '||v_raw2||'.');
dbms_output.put_line( 'Number whole years difference between d1 and d3 is '||years2||'.');
dbms_output.put_line( 'Number whole days difference, using the remainder function, between d1 and d3 is '||v_rem2||'.');
dbms_output.put_line( 'Number whole days difference, using the modulus function, between d1 and d3 is '||v_mod2||'.');
END;


The code above produces the following output:

D1 is 09-JUL-2007.
D2 is 24-JUL-1995.
D3 is 24-JUN-1995.
Raw difference between d1 and d2 is 4368.
Number whole years difference between d1 and d2 is 11.
Number whole days difference, using the remainder function, between d1 and d2 is -12.
Number whole days difference, using the modulus function, between d1 and d2 is 353.
Raw difference between d1 and d3 is 4398.
Number whole years difference between d1 and d3 is 11.
Number whole days difference, using the remainder function, between d1 and d3 is 18.
Number whole days difference, using the modulus function, between d1 and d3 is 18.


D1 represents the current date (SYSDATE was not used to preserve consistency in the results). Although D1 is greater than both D2 and D3, the REMAINDER function is somehow producing a negative result for D2 (D3 calculations come out fine). I would not have expected that a negative result is possible since D2 occurs almost 12 years prior to D1!

It appears that, for some reason, REMAINDER is converting the dates to the Julian dates (day of the year) and somehow ignores the year for the calculations. This is clearly incorrect. I could not find any reference to this anomaly in the SQL reference; I am uncertain whether this is a known issue.

Your insight would be appreciated. Thanks in advance.

and Tom said...

first, I would not use 365 - that is just wrong.

You need to use months between at best.

You should could use intervals as well.


neither of your answers is technically correct

ops$tkyte%ORA10GR2> DECLARE
  2          d1 DATE;
  3          d2 DATE;
  4          d3 DATE;
  5          years   number;
  6          days    number;
  7  BEGIN
  8          d1 := to_date( '09-JUL-2007', 'DD-MON-YYYY' );--SYSDATE;
  9          d2 := to_date( '24-JUL-1995', 'DD-MON-YYYY' );
 10          d3 := to_date( '24-JUN-1995', 'DD-MON-YYYY' );
 11
 12          years := trunc( months_between( d1, d2 ) / 12 );
 13          days  := d1 - add_months( d2, years*12 );
 14
 15          dbms_output.put_line( 'years = ' || years || ' days = ' || days );
 16  end;
 17  /
years = 11 days = 350

PL/SQL procedure successfully completed.



mixing days and years is wrought with dangers (like months with days is) since neither a year nor a month is of fixed duration.....






Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 10, 2007 - 2:02 pm UTC

Steven:

I agree with Tom's points but it doesn't address your fundamental question about why REMAINDER and MOD are returning different values for some inputs.

You said "It appears that, for some reason, REMAINDER is converting the dates to the Julian dates (day of the year) and somehow ignores the year for the calculations".

REMAINDER deals with numbers, plain and simple. It doesn't know that one of the numbers you passed was calculated from dates. You get the same result if you pass numeric literals:

SQL> select remainder(4368,365) from dual;

REMAINDER(4368,365)
-------------------
-12

SQL> c/remainder/mod
1* select mod(4368,365) from dual
SQL> /

MOD(4368,365)
-------------
353


The answer to your question is right in the docs. The formula for MOD (for non-negative m) is:

MOD(m,n) = m - n * FLOOR(m/n)

and the doc for REMAINDER says that it uses ROUND instead of FLOOR, i.e.:

REMAINDER(m,n) = m - n * ROUND(m/n)

So, it should be obvious if you plug your values into these formulas that they are supposed to give different results.

You could describe REMAINDER's behavior as: Give me the smallest-magnitude integer value that can be added to m to produce a multiple of n. Sometimes that will be a negative number.
Tom Kyte
July 10, 2007 - 8:04 pm UTC

yeah, thanks.

when the solution is wrong - I ignore it (and the question) and give what I believe to be the correct solution :)

I hate debugging stuff that doesn't work and explaining why it doesn't work - when giving a working solution is so much easier...

Use of ROUND in REMAINDER

Steven Westenzweig, July 10, 2007 - 2:45 pm UTC

Thanks for the response. I agree that attempting to use constants for years and months produces inaccurate results; the method you proposed works much better.

What I still have a problem with, and what the first reviewer touched on, was the actual REMAINDER function. I conducted a bit more research after I submitted my question and came to the conclusion that the REMAINDER function is not mathematically correct.

By applying the division algorithm (a = qd + r), my date example becomes:
4368 = ( 11*365 ) + remainder

If you apply the remainder from the MOD example (353), the formula holds true. If you apply the remainder from the REMAINDER function example (-12), the numbers do not add up.

The problem is in ORACLE's use of the ROUND function on the quotient, which is fundamentally wrong. This results in the REMAINDER function producing a negative result whenever the quotient is rounded up. Quite clearly, the REMAINDER function does not accurately perform its documented purpose.
Tom Kyte
July 10, 2007 - 8:09 pm UTC

umm, it does precisely what it is documented to do?

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions133.htm#i1300767


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library