Difference between two dates [message #670222] |
Tue, 19 June 2018 14:06 |
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 |
|
Michel Cadot
Messages: 68693 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?
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 |
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 #670268 is a reply to message #670231] |
Wed, 20 June 2018 11:59 |
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;
|
|
|
|
|