return the month in between sql

Question

I want to ask is there any way in PL/SQL that I can return the months in between user input. For example, the user put in

DateStart: 01/2019   and
DateEnd: 05/2019

The query should return

01/2019, 02/2019, 03/2019, 04/2019,05/2019.

This post from Stackoverflow might help your query . https://stackoverflow.com/questions/7885851/months-between-two-dates] — Umeshwaran, Dec 04 '19 at 11:32
Of course there is a way to do this. This is like the main purpose of SQL. Also depends if you are querying a table or if you want a script to just output the above. — Ergi Nushi, Dec 04 '19 at 11:41

score 0 · Answer 1 · answered Dec 04 '19 at 11:43

You can do this in sql, no pl/sql needed for this. Convert month/year to a date, 1st of the month.

Then count the months between the first and last argument.

Then select doing a connect by level + 2 (because you're including the boundary values). All put together this gives:

WITH test_data (monthyear_start, monthyear_end) AS
(
  SELECT '01/2019', '05/2019' FROM DUAL
),
monthcount(months) AS
(
  SELECT MONTHS_BETWEEN(TO_DATE('01/'||monthyear_end,'DD/MM/YYYY'),TO_DATE('01/'||monthyear_start,'DD/MM/YYYY')) FROM test_data
)
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/'||monthyear_start,'DD/MM/YYYY'),LEVEL - 1),'MM/YYYY')  FROM test_data, monthcount
CONNECT BY LEVEL < months + 2;

01/2019
02/2019
03/2019
04/2019
05/2019

Barbaros Özhan · Answer 2 · 2019-12-04T16:27:35.387

You can firstly use connect by level <= syntax, and then apply listagg() function to concatenate the strings :

with t2 as
(
select distinct to_char( to_date(DateStart,'mm/yyyy') + level - 1 , 'mm/yyyy') as mnt 
  from t
 connect by level <= to_date(DateEnd,'mm/yyyy') - to_date(DateStart,'mm/yyyy') + 1 
)
select listagg(mnt,',') within group 
       (order by to_number(substr(mnt,-4)||substr(mnt,1,2))) as "Months"
  from t2

Demo

score 0 · Answer 3 · answered Dec 04 '19 at 18:48

Similar, but yet different, maybe even simpler than previous suggestions:

SQL> with test (dstart, dend) as
  2    (select '01/2019', '05/2019' from dual)
  3  select to_char(add_months(to_date(dstart, 'mm/yyyy'), level - 1), 'mm/yyyy') result
  4  from test
  5  connect by
  6    level <= months_between(to_date(dend, 'mm/yyyy'), to_date(dstart, 'mm/yyyy')) + 1;

RESULT
-------
01/2019
02/2019
03/2019
04/2019
05/2019

SQL>

davidm · Answer 4 · 2019-12-11T14:35:58.260

Here is a PL/SQL procedure and some test code.

DECLARE 

    PROCEDURE p_list_months(start_date IN DATE, end_date IN DATE) AS
         v_count INTEGER := 0;
    BEGIN
        v_count := MONTHS_BETWEEN(end_date, start_date);
        FOR i in 0..v_count LOOP
             dbms_output.put_line(to_char(add_months(start_date, i),'mm/yyyy')); 
        END LOOP;
    END;


BEGIN
   p_list_months(SYSDATE, ADD_MONTHS(SYSDATE, 5));
END;

And a SQL query:


-- start_date 10/2019 end_date 10/2020
SELECT to_char(add_months(TO_DATE('10/2019','mm/yyyy'), LEVEL-1), 'mm/yyyy') dat 
    FROM dual 
    CONNECT BY LEVEL <= (MONTHS_BETWEEN( TO_DATE('10/2020','mm/yyyy'),
                                          TO_DATE('10/2019','mm/yyyy'))) + 1;

return the month in between sql

4 Answers4