Get month days between 2 dates

Question

What is the easiest way to find difference in days between 2 dates separately for each month.

Let's suppose I have two dates
1.25 May 2016
2.08 Jun 2016

The result I want to obtain is number of days for May and numbers of Day for Jun like this (201605-6,201606-8).

Which is the best method to do this?

what is your desire output with this input: 1.`25 May 2015` 2.`08 Jun 2016` ? multiple rows? string seperated by comma?... — Pham X. Bach, Jun 10 '16 at 07:33
String separated comma in exactly this format 201605-6,201606-8. YearMonth-Numberofdays,yearmonth-numberofdays ... — jonny, Jun 10 '16 at 07:35
Are the 2 dates in a row of a table? Can the dates be multiple years apart? — Paul Maxwell, Jun 10 '16 at 07:45
yes the date are in the same row and the can be multiple years apart — jonny, Jun 10 '16 at 07:49

score 0 · Answer 1 · edited May 23 '17 at 11:52

In your case, start_date and end_date are in the same row of a table so one solution is:

Idea: create a function with 2 input: start_date and end_date, output: string_with_days_in_each_month_between_two_date

Practice: First, create a function GET_DAYS_EACH_MONTH_BETWEEN return the expected string with each start_date and end_date

CREATE OR REPLACE FUNCTION GET_DAYS_EACH_MONTH_BETWEEN
(start_date IN date, end_date IN date)
    RETURN  varchar2 IS
    result varchar2(4000);
BEGIN 
    WITH tmp AS
    (SELECT  
        LAST_DAY(ADD_MONTHS( start_date, level-1 ))
    FROM 
    (SELECT 
        start_date
        ,end_date
    FROM 
        dual)
    CONNECT BY 
        level <= MONTHS_BETWEEN(
                               TRUNC(end_date,'MM'),
                               TRUNC(start_date,'MM') )
    )
    , tmp1 AS
    (
    SELECT start_date date_col  FROM dual 
    UNION ALL
    SELECT end_date from dual
    UNION ALL
    SELECT * FROM tmp
    )
    SELECT 
        LISTAGG(result_col, ',') WITHIN GROUP (ORDER BY date_col) into result
    FROM 
        (SELECT 
            TO_CHAR(date_col,'yyyyMM') || '_' || TO_CHAR(date_col - LAG(date_col) OVER (ORDER BY date_col)) result_col
            , date_col
            ,ROW_NUMBER() OVER (ORDER BY date_col) rwn
        FROM
            tmp1
        ORDER BY 
            date_col 
        )
    WHERE 
        rwn <> 1;

    RETURN result;

END;

Second, apply your function to your table

WITH test_table AS
(
 SELECT 
    TO_DATE('2016-02-06','yyyy-mm-dd') start_date
    ,TO_DATE('2016-06-08','yyyy-mm-dd') end_date 
FROM 
    DUAL
 UNION ALL
 SELECT 
    TO_DATE('2016-05-06','yyyy-mm-dd')
    ,TO_DATE('2016-06-08','yyyy-mm-dd') 
FROM 
    DUAL
 )
SELECT 
    start_date, end_date
    ,GET_DAYS_EACH_MONTH_BETWEEN(start_date, end_date) result_col
FROM 
    test_table;

Result:

start_date  end_date    result_col
2/6/2016    6/8/2016    201602_23,201603_31,201604_30,201605_31,201606_8
5/6/2016    6/8/2016    201605_25,201606_8

IMPORTANCE NOTICE: the result string only have 4000 char in length, so you should consider when start_date and end_date is too far from each

The function using suggested result from this link to gerenate all months between 2 given date.

vercelli · Answer 2 · 2016-06-10T09:59:25.327

I made a recursive approach:

with recur (dias, texto, nivel, d1,d2) as 
              (select last_day(d1)-d1 as dias, to_char(d1,'yyyymm')||'-' as texto,  0 as nivel, d1,d2 from dates union all --first date
               select case when (d2-trunc(add_months(d1, nivel+1),'month')+1) > extract(day from last_day(add_months(d1, nivel+1))) 
                       then extract(day from last_day(add_months(d1, nivel+1)))  
                       else d2-trunc(add_months(d1, nivel+1),'month')+1 end as dias, 
                       to_char(add_months(d1, nivel+1),'yyyymm')||'-' as texto,  nivel+1 as nivel ,d1,d2 from recur where trunc(add_months(d1, nivel+1),'month') < d2  --last month
               )
select d1, d2,listagg(texto||to_char(dias),',') within group (order by nivel) as resultado
 from recur
 group by d1,d2
 order by d1,d2;

OUTPUT for 25 May 2016 to 08 Jun 2016

25-MAY-16   08-JUN-16   201605-6,201606-8

OUTPUT for 25 May 2016 to 08 Jul 2017

25-MAY-16   08-JUL-17   201605-6,201606-30,201607-31,201608-31,201609-30,201610-31,201611-30,201612-31,201701-31,201702-28,201703-31,201704-30,201705-31,201706-30,201707-8

Get month days between 2 dates

2 Answers2