3 Replies Latest reply: Jul 18, 2011 9:26 AM by user1991209 RSS

    SQL Query help for month end dates

    user1991209
      Need help for a query to generate a list for all month end dates from Jan 31,2000 to current month end and then another 12 months into the furure.

      Thanks
      Paul
        • 1. Re: SQL Query help for month end dates
          Centinul
          Something like this should work:
          SELECT ADD_MONTHS(TO_DATE('01/31/2000','MM/DD/YYYY'),LEVEL - 1) AS m_end
          FROM   dual
          CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(LAST_DAY(SYSDATE),12),TO_DATE('01/31/2000','MM/DD/YYYY')) + 1
          • 2. Re: SQL Query help for month end dates
            user130038
            Following should work for " *until current month* "
            select last_day(add_months (trunc (to_date('01/01/2000','MM/DD/YYYY'), 'MM'), 1*level -1))
            month   from dual
            connect by level <= months_between(sysdate, to_date('01/01/2000','MM/DD/YYYY')) + 1
            order by month                                
            EDIT: Centinul beat me do this!

            Edited by: iDBA on Jul 18, 2011 7:17 AM


            And this should work for " *until 12-months in future* "
            select last_day(add_months (trunc (to_date('01/01/2000','MM/DD/YYYY'), 'MM'), 1*level -1))
            month   from dual
            connect by level <= months_between(add_months(sysdate, 12), to_date('01/01/2000','MM/DD/YYYY')) + 1
            order by month 
            • 3. Re: SQL Query help for month end dates
              user1991209
              Excellent.
              Really appreciate the help.
              Thanks
              Paul