SWAT Blog

Archive for January, 2014

Generate Date Ranges in Oracle PL/SQL

Tuesday, January 28th, 2014

Here is an easy way to generate date ranges in oracle pl/sql:

WITH t AS
  (SELECT to_date('01-OCT-2012') start_date,
    to_date('30-SEP-2013') end_date
  FROM dual
  )
  SELECT add_months(TRUNC(start_date,'mm'),LEVEL      - 1) START_DATE,
    add_months(add_months(TRUNC(start_date,'mm'),LEVEL - 1),1)-1 END_DATE
  FROM t
    CONNECT BY TRUNC(end_date,'mm') >= add_months(TRUNC(start_date,'mm'),LEVEL - 1)

OUTPUT:

START_DATE  END_DATE  
----------- -----------
01-OCT-2012 31-OCT-2012 
01-NOV-2012 30-NOV-2012 
01-DEC-2012 31-DEC-2012 
01-JAN-2013 31-JAN-2013 
01-FEB-2013 28-FEB-2013 
01-MAR-2013 31-MAR-2013 
01-APR-2013 30-APR-2013 
01-MAY-2013 31-MAY-2013 
01-JUN-2013 30-JUN-2013 
01-JUL-2013 31-JUL-2013 
01-AUG-2013 31-AUG-2013 
01-SEP-2013 30-SEP-2013 

 12 rows selected
  • © 2004-2015 Special Work & Technology Limited