How to get a list of months between 2 given dates using a query?

Question

I have 2 dates, say 28-Mar-2011 and 29-Jun-2011. I need an sql query that will display the months between these 2 dates including the months containing the dates, ie. June, May, April and March.

score 22 · Accepted Answer · edited Jul 23 '13 at 15:20

22

Something like this

SQL> ed
Wrote file afiedt.buf

    select to_char( add_months( start_date, level-1 ), 'fmMonth' )
      from (select date '2011-03-30' start_date,
                   date '2011-06-29' end_date
              from dual)
     connect by level <= months_between(
                           trunc(end_date,'MM'),
                           trunc(start_date,'MM') )
  *                      + 1
SQL> /

TO_CHAR(ADD_MONTHS(START_DATE,LEVEL-
------------------------------------
March
April
May
June

should work.

edited Jul 23 '13 at 15:20

Grzegorz Gierlik

11,112
4
47
55

answered Aug 03 '11 at 13:52

Justin Cave

227,342
24
367
384

1

Well, even a blind squirrel finds the occasional nut. – Justin Cave Aug 03 '11 at 14:52
On oracle, in my environment, the above missed out the last month(June). Not sure if anyone else had the same issue? I wonder what the difference could be. – SlackGadget Dec 12 '22 at 12:25

score 3 · Answer 2 · answered Jul 23 '13 at 16:30

Gonna add this solution just because I think it's much cleaner than the others:

SELECT ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) date_out
FROM   DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
    <= TRUNC(TO_DATE('29-Jun-2011', 'DD-MON-YYYY'), 'MON')

score 1 · Answer 3 · answered Sep 21 '12 at 14:30

I needed an answer to this a couple of days ago. I found another solution I liked more:

select to_char(which_month, 'Mon-yyyy') month
from
(
    select
        add_months(to_date(:start_date,'mm-yyyy'), rownum-1) which_month
    from
        all_objects
    where
        rownum <= months_between(to_date(:end_date,'mm-yyyy'), add_months(to_date(:start_date,'mm-yyyy'), -1))
    order by
        which_month
)

You could of course use any format you want. I 'union'ed and summed over another set so that I'd get the months even when they didn't have results.

Appreciated, exact answer what I want :) – John Nov 29 '15 at 08:42 — John, Nov 29 '15 at 08:42

bruno · Answer 4 · 2011-08-03T13:26:22.337

1

You can use the function MONTHS_BETWEEN

SELECT MOD( TRUNC( MONTHS_BETWEEN( '2011-07-29', '2011-03-28' ) ), 12 ) as MONTHS
FROM DUAL

Output

    MONTHS
----------
         4

edited Aug 03 '11 at 13:26

answered Aug 03 '11 at 13:18

bruno

2,802
1
23
23

I need the months as the output, ie. March, April, May and June. Not 4. – Nitish Aug 03 '11 at 13:41
Sorry, I misunderstood your question. You should check Justin Cave's answer. It is correct. – bruno Aug 03 '11 at 14:15

score 0 · Answer 5 · edited Apr 21 '17 at 02:10

SELECT MIN (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) F_DATE,
         MAX (to_date((TO_CHAR (Actual_Date, 'DD-MM-RRRR')),'dd-mm-rrrr')) T_DATE,
         TO_CHAR (Actual_Date, 'MM-RRRR') TRX_MONTH     
    FROM (    SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr')) + LEVEL - 1
                        Actual_Date
                FROM (SELECT TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'), 'MM') - 1
                                AS dt
                        FROM DUAL)
          CONNECT BY LEVEL <=
                        (  TO_DATE (:P_TDATE, 'dd-mm-rrrr')
                         - TRUNC (TO_DATE (:P_FDATE, 'dd-mm-rrrr'))
                         + 1))
GROUP BY TO_CHAR (Actual_Date, 'MM-RRRR')
ORDER BY 1

Please indent your code by 4 spaces to activate syntax highlighting. Also, adding a bit of explaining text is usually appreciated (especially if there are accepted answers already, say what your solution makes different). — chtz, Dec 24 '16 at 09:59

score 0 · Answer 6 · edited Jan 15 '19 at 13:39

     declare 
        v_date_from_first_day date;
        v_date_to_last_day date;
        v_month_name varchar2(10);
        v_month_number number;
        v_year_number number;
        v_month_diff number;
        begin
          v_date_to_last_day := to_date('31.12.2018'); 
          v_date_from_first_day := to_date('01.01.2018');

          select months_between(v_date_to_last_day,v_date_from_first_day) as diff into v_month_diff from dual;

          for i in 1..round(v_month_diff, 2) loop
          select 
          to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'fmMonth') as month_nm,
          to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'MM') as month_num,
          to_char(trunc(add_months(v_date_to_last_day - months_between(v_date_from_first_day, v_date_to_last_day), -i)), 'YYYY') as year_num
          into v_month_name, v_month_number, v_year_number
          from dual;

          dbms_output.put_line(v_month_number || '/' || v_year_number);
dbms_output.put_line(v_month_name || '/' || v_year_number);
          end loop;
        end;

        Output: 
        12/2018
        11/2018
        10/2018
        9/2018
        8/2018
        7/2018
        6/2018
        5/2018
        4/2018
        3/2018
        2/2018
        1/2018

Here, month names are in Croatian

Prosinac/2018
Studeni/2018
Listopad/2018
Rujan/2018
Kolovoz/2018
Srpanj/2018
Lipanj/2018
Svibanj/2018
Travanj/2018
Ožujak/2018
Veljača/2018
Siječanj/2018

Hi and welcome to SO, please read the guide on [answer]. You might also document your Codesample a bit better to help people understand what it does. — LuckyLikey, Jan 15 '19 at 12:28
never mind :) Just be sure to correct it, to make it as useful as possible for anyone reading it. — LuckyLikey, Jan 15 '19 at 14:57

How to get a list of months between 2 given dates using a query?

6 Answers6

Linked