How to Generate Date Ranges in Oracle SQL


I’ve just come across an interesting SQL question here on CodeRanch. How to generate date ranges in SQL, given any input date. The question didn’t specify the SQL dialect, so let’s choose Oracle SQL, which features the awesome CONNECT BY clause. The requirements specify that given any input date:

  • Date ranges span 12 months
  • The first date range starts at the input date
  • The last date range includes today
  • Subsequent date ranges repeat the same date

For example, using 2010-06-10 we would get:

START_DATE   END_DATE
-----------------------
2010-06-10   2011-06-10
2011-06-10   2012-06-10
2012-06-10   2013-06-10
2013-06-10   2014-06-10 <-- This includes today, 2013-07-24

It’s actually very simple:

SELECT
  add_months(input, (level - 1) * 12) start_date,
  add_months(input,  level      * 12) end_date
FROM (
  -- Set the input date here
  SELECT DATE '2010-06-10' input
  FROM DUAL
)
CONNECT BY
  add_months(input, (level - 1) * 12) < sysdate

See the SQL Fiddle here to see the above in action: http://sqlfiddle.com/#!4/d41d8/14448

One thought on “How to Generate Date Ranges in Oracle SQL

  1. oracle 12c

    declare v_sDate date := '01-Jun-2016';
    v_eDate date := v_sDate + 100000;
    c sys_refcursor;
    begin
    open c for '
    with tx (dt) as (
    select :1 from dual
    union all
    select dt+1 from tx
    where dt+1 <= :2
    )
    select * from tx' using v_sDate,v_eDate;
    sys.dbms_sql.return_result(c);
    end;
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s