Search code examples
sqloracle-databaseibatis

Get the data between two months in ibatis

If I want to get the data between March 2013 to November( for each month, all daily data are to be summed up to make a monthly data), I thought what I did was correct. But it gives me 186 rows(all dates from March to November) of result instead of giving just 9 ( 9 months between the two).

And for some reason, I get only 1 data for November.

Could anyone fix this?

 SELECT 
     TO_CHAR (TO_DATE ('201305','YYYYMM')+LEVEL- 1, 'YYYYMM') AS MONTH
        FROM DUAL
        CONNECT BY TO_DATE('201305', 'YYYYMM') + LEVEL - 1  <=  TO_DATE('201311', 'YYYYMM')
Solution

  • Is this what you want?

     SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201303','YYYYMM'),LEVEL-1), 'YYYYMM') AS Month
   FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE('201303','YYYYMM'),LEVEL-1 ) <= TO_DATE('201311', 'YYYYMM');

    Output:

    Month
-------
201303
201304
201305
201306
201307
201308
201309
201310
201311