How to calculating the number of days between two dates assuming every month has 31 days in Oracle-CodePudding

How would you go about calculating the number of days between two dates while assuming every month has 31 days in Oracle?

select to_date('20210201','YYYYMMDD') - to_date('20210128','YYYYMMDD') from dual; --result 4
select to_date('20210301','YYYYMMDD') - to_date('20210228','YYYYMMDD') from dual; --result 1, expected 4

CodePudding user response：

Does something like this work for you:

select 
(extract(year from date '2021-03-01') - extract(year from date '2021-02-28')) * 31 * 12
  (extract(month from date '2021-03-01') - extract(month from date '2021-02-28')) * 31
  (extract(day from date '2021-03-01') - extract(day from date '2021-02-28'))
as diff
from dual;

Fiddle

CodePudding user response：

Using @Zakaria's input, I came up with the following solution:

create or replace FUNCTION DAYS_BETWEEN_DATES_31
    (in_from IN DATE
    ,in_to   IN DATE
    )
RETURN NUMBER
IS
    v_difference NUMBER;
    v_note VARCHAR2 (100) DEFAULT '   ';
BEGIN
    --calculate the number of days in between the two dates assuming every month has 31 days
    v_difference :=
    (extract(year from  in_to) - extract(year from in_from)) * 31 * 12
      (extract(month from  in_to) - extract(month from in_from)) * 31
      (extract(day from in_to) - extract(day from in_from))
      1;

    --compensate for months that have less than 31 days
    IF (extract(month from in_to) = 2
        OR extract(month from in_to) = 4
        OR extract(month from in_to) = 6
        OR extract(month from in_to) = 9
        OR extract(month from in_to) = 11)
    AND extract(day from last_day(in_to)) = extract(day from in_to)
    THEN
        v_note := '  '||to_char(31 - extract(day from in_to));
        v_difference := v_difference   (31 - extract(day from in_to));
    END IF;

    DBMS_OUTPUT.PUT_LINE('from:'|| to_char(in_from,'DD/MM/YYYY') || ' to:'|| to_char(in_to,'DD/MM/YYYY') || v_note || ' : ' ||v_difference);
    RETURN v_difference;
END
;

Result:

from:01/12/2021 to:30/12/2021    : 30
from:01/12/2021 to:31/12/2021    : 31
from:01/12/2021 to:31/01/2022    : 62
from:01/12/2021 to:28/02/2022  3 : 93
from:05/01/2022 to:04/02/2022    : 31
from:05/01/2022 to:04/03/2022    : 62
from:05/08/2022 to:30/09/2022  1 : 58
from:05/08/2022 to:04/10/2022    : 62
from:05/08/2022 to:04/11/2022    : 93
from:25/08/2022 to:30/11/2022  1 : 100
from:25/11/2022 to:30/11/2022  1 : 7
from:05/08/2022 to:04/12/2022    : 124
from:05/08/2022 to:31/12/2022    : 151
from:01/01/2022 to:31/12/2022    : 372