Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two dates
Difference between two dates [message #670222] Tue, 19 June 2018 14:06 Go to next message
desmond30
Messages: 41
Registered: November 2009
Member
I want to find out difference between two dates..so in Oracle, date 1 - date 2 should give the number of days by default

declare
x date:=to_date('31-OCT-2018','DD-MON-YYYY');
y date:=to_date('01-APR-2018','DD-MON-YYYY');
v_months number;
v_days number;
begin

select round(months_between(x,y))
into v_months
from dual;

select trunc(x - y)
into v_days
from dual;

dbms_output.put_line('v_months: '||v_months||' v_days: '||v_days);

end;


when I print v_days, it gives 213 days, but when I count the days from april 2018 to october 2018, we get 214 days, why this difference ?
Re: Difference between two dates [message #670225 is a reply to message #670222] Tue, 19 June 2018 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
when I print v_days, it gives 213 days, but when I count the days from april 2018 to october 2018, we get 214 days, why this difference ?
You don't count very well? Smile
If you count the days from the complete months between April and October then you count the number of days from 01-APR (00:00) to 01-NOV (00:00).

Note you don't need to use any SQL, ROUND, MONTHS_BETWEEN and TRUNC are also PL/SQL functions, just use ":=".

Re: Difference between two dates [message #670227 is a reply to message #670225] Tue, 19 June 2018 14:22 Go to previous messageGo to next message
desmond30
Messages: 41
Registered: November 2009
Member
so I used trunc for my x and y variables to take exact full day, then I simply used :=

declare
x date:= trunc(to_date('31-OCT-2018','DD-MON-YYYY'));
y date:= trunc(to_date('01-APR-2018','DD-MON-YYYY'));
v_months number;
v_days number;
begin

select round(months_between(x,y))
into v_months
from dual;

v_days:=x-y;

dbms_output.put_line('v_months: '||v_months||' v_days: '||v_days);

end;


still gives 213 days, But when I count the days looking at calendar

April - 30
May - 31
June - 30
July - 31
August - 31
September - 30
October - 31

this gives a total of 214 !
Re: Difference between two dates [message #670228 is a reply to message #670227] Tue, 19 June 2018 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The difference between 01-APR and 02-APR is 1 day.
The difference between 01-APR and 03-APR is 2 days.
...
The difference between 01-APR and 30-APR is 29 days, NOT 30.
...

In your computation you do NOT count the last day of the last month.

Re: Difference between two dates [message #670229 is a reply to message #670228] Tue, 19 June 2018 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See:
SQL> select months_between(to_date('31-OCT-2018','DD-MON-YYYY'),to_date('01-APR-2018','DD-MON-YYYY')) from dual;
MONTHS_BETWEEN(TO_DATE('31-OCT-2018','DD-MON-YYYY'),TO_DATE('01-APR-2018','DD-MON-YYYY'))
-----------------------------------------------------------------------------------------
                                                                               6.96774194
It is NOT 7 months but 7 months minus 1 day.
Re: Difference between two dates [message #670230 is a reply to message #670228] Tue, 19 June 2018 14:32 Go to previous messageGo to next message
desmond30
Messages: 41
Registered: November 2009
Member
Yes, that is correct, I just realized that, when I gave the x and y variables to be 30th april and 1st april...gives 29, looks like that is how Oracle computes..Sad..Thanks !


Re: Difference between two dates [message #670231 is a reply to message #670230] Tue, 19 June 2018 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Er, this is how everyone compute.
For everyone, the difference between 31 and 1 is 30.

Re: Difference between two dates [message #670268 is a reply to message #670231] Wed, 20 June 2018 11:59 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You can't count the first day, it is 213

DECLARE
    X          DATE := TO_DATE('31-OCT-2018', 'DD-MON-YYYY');
    Y          DATE := TO_DATE('01-APR-2018', 'DD-MON-YYYY');
    V_months   NUMBER;
    V_days     NUMBER;
BEGIN
    SELECT TRUNC(MONTHS_BETWEEN(X, Y)) INTO V_months FROM DUAL;

    V_days := X - ADD_MONTHS(Y, V_months);

    DBMS_OUTPUT.Put_line('v_months: ' || V_months || ' v_days: ' || V_days ||
                         '  total days: ' || TRUNC(X - Y));
END;
Re: Difference between two dates [message #670269 is a reply to message #670268] Wed, 20 June 2018 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Actually, as default time is 00:00, it is the last day that is not counted.

Re: Difference between two dates [message #670301 is a reply to message #670269] Mon, 25 June 2018 06:48 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
True, lol
Previous Topic: Compare current and previous record
Next Topic: Fetching Unix Folder Name and Timestamp in SQL/PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 11:47:27 CDT 2024