CODEWITHSUNDEEP
sqloracle-database
user2638894
user2638894

Reputation: 303

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

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

Upvotes: 1

Views: 87

Answers (2)

user2638894
user2638894

Reputation: 303

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_result NUMBER := 0;
    v_compensation NUMBER := 0;
BEGIN
    /*
    This function returns the number of days in a data range assuming every month has 31 days
    */
 
    --calculate the number of days in between the two dates assuming every month has 31 days
    v_result :=
    (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;
 
    --if the in_to date is the last day of the month and less than 31, we should compensate for it
    IF extract(day from last_day(in_to)) = extract(day from in_to)
    AND extract(day from in_to) < 31
    THEN
        v_compensation := 31 - extract(day from in_to);
        v_result := v_result + v_compensation;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('from:'|| to_char(in_from,'DD/MM/YYYY') || ' to:'|| to_char(in_to,'DD/MM/YYYY') || ' + ' || v_compensation || ' : ' ||v_result);
    RETURN v_result;
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

Upvotes: 0

Zakaria
Zakaria

Reputation: 4806

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

Upvotes: 1

Related Questions