Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kumar.

Asked: June 16, 2018 - 11:53 am UTC

Last updated: June 19, 2018 - 3:20 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi tom,
I have one question in which suppose i take two date range '10-jun-2014' and '10-jun-2018' then i want the output like
10-jun-2014 to 10-jun-2015
10-jun-2015 to 10-jun-2016
10-jun-2016 to 10-jun-2017
10-jun-2017 to 10-jun-2018

Can you please help

and Connor said...

We can generate the rows using a CONNECT BY trick

SQL> create table t ( dfrom date, dto date );

Table created.

SQL>
SQL> insert into t values ( date '2014-06-10', date '2018-06-10' );

1 row created.

SQL>
SQL> select add_months(dfrom,12*(rownum-1))
  2  from t
  3  connect by add_months(dfrom,12*(rownum-1)) <= dto;

ADD_MONTH
---------
10-JUN-14
10-JUN-15
10-JUN-16
10-JUN-17
10-JUN-18

5 rows selected.


so then we just manipulate that to get our answer.

SQL> select
  2    add_months(dfrom,12*(rownum-1)),
  3    add_months(dfrom,12*rownum)
  4  from t
  5  connect by add_months(dfrom,12*(rownum-1)) < dto;

ADD_MONTH ADD_MONTH
--------- ---------
10-JUN-14 10-JUN-15
10-JUN-15 10-JUN-16
10-JUN-16 10-JUN-17
10-JUN-17 10-JUN-18


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Lovely Rani, June 18, 2018 - 9:23 am UTC

Thanks Connor for your quick response. It is helpful for me
Connor McDonald
June 19, 2018 - 3:20 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library