You Asked

Hi Tom,

How can I split a parameterised date range into months with start and end date for each month.

my stored procedure takes FromDate and ToDate parameters with certain range (Ex: '1-Mar-2011' to '31-Dec-2011') and in SP I want to divide this range into months with start and end date values which will be used in further query to get data between each start and end date.

So the date range should get split as:

From - To
1-Mar-2011 - 31-Mar-2011
1-Apr-2011 - 30-Apr-2011
1-May-2011 - 31-May-2011
.
.
1-Dec-2011 - 31-Dec-2011

I tried using CONNECT by clause, rownum value with all_objects table but could not achieve the desired result.
Please suggest how we can write the query.

Thanks
MB

and Tom said...

ops$tkyte%ORA11GR2> variable sdate varchar2(30);
ops$tkyte%ORA11GR2> variable edate varchar2(30);
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :sdate := '01-mar-2011'; :edate := '31-dec-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select level r,
  2         greatest( add_months(trunc(sdate,'mm'),level-1), sdate ),
  3         least( last_day( add_months(sdate,level-1) ), edate )
  4    from (select to_date( :sdate, 'dd-mon-yyyy' ) sdate,
  5                 to_date( :edate, 'dd-mon-yyyy' ) edate
  6            from dual)
  7  connect by level <= months_between( trunc( edate,'mm'), trunc(sdate,'mm') ) + 1
  8  /

         R GREATEST( LEAST(LAS
---------- --------- ---------
         1 01-MAR-11 31-MAR-11
         2 01-APR-11 30-APR-11
         3 01-MAY-11 31-MAY-11
         4 01-JUN-11 30-JUN-11
         5 01-JUL-11 31-JUL-11
         6 01-AUG-11 31-AUG-11
         7 01-SEP-11 30-SEP-11
         8 01-OCT-11 31-OCT-11
         9 01-NOV-11 30-NOV-11
        10 01-DEC-11 31-DEC-11

10 rows selected.

ops$tkyte%ORA11GR2> exec :sdate := '15-mar-2011'; :edate := '25-dec-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

         R GREATEST( LEAST(LAS
---------- --------- ---------
         1 15-MAR-11 31-MAR-11
         2 01-APR-11 30-APR-11
         3 01-MAY-11 31-MAY-11
         4 01-JUN-11 30-JUN-11
         5 01-JUL-11 31-JUL-11
         6 01-AUG-11 31-AUG-11
         7 01-SEP-11 30-SEP-11
         8 01-OCT-11 31-OCT-11
         9 01-NOV-11 30-NOV-11
        10 01-DEC-11 25-DEC-11

10 rows selected.

I used greatest and least in case you need to start/end in the middle of months as well - you didn't say. If not - if the sdate is always the first and the edate is always the last - you can simplify this further by getting rid of that bit.

Rating

(3 ratings)

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

Comments

perfect solution

M B, December 30, 2011 - 5:20 am UTC

Hi tom,

Thanks for your quick response. this is the query I exactly wanted. it worked perfect for my scenario.

Thanks again.

Jay, December 06, 2012 - 7:54 am UTC

Hi Tom,

I am trying to use your query in one of my scenerio's on a table which has the date ranges. However the query runs fine and produces correct results only for 2 or 3 records. For any more than 3 records, the query takes very long and gets stuck.. any thoughts?...
thanks
Jay

query
------
select distinct level r,full_name,assignment_number,sdate,edate,
greatest( add_months(trunc(sdate,'mm'),level-1), sdate ) period_start,
least( last_day( add_months(sdate,level-1) ), edate ) period_end
from (select to_date( decode(to_char(effective_start_date,'RRRR'), '2011', '1-JAN-2012', '2010', '1-JAN-2012', effective_start_date), 'dd-mon-RRRR' ) sdate,
to_date( decode(effective_end_date, '31-DEC-4712', '31-DEC-2012', effective_end_date), 'dd-mon-RRRR' ) edate, full_name, assignment_number
from test_pen_slice
)
connect by level <= months_between( trunc( edate,'mm'), trunc(sdate,'mm') ) + 1
order by 2,5

sample table .. 5 records
--------------------------
SET DEFINE OFF;
Insert into TEST_PEN_SLICE
(FULL_NAME, ASSIGNMENT_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('ABAYASEKARA, Mr. Mohan', '60', TO_DATE('04/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_PEN_SLICE
(FULL_NAME, ASSIGNMENT_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('ABAYASEKARA, Mr. Mohan', '60', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_PEN_SLICE
(FULL_NAME, ASSIGNMENT_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('ABD ELSAID, Mr. Mohamed M.', '409', TO_DATE('01/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_PEN_SLICE
(FULL_NAME, ASSIGNMENT_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('ABRATE, Mr. Tommaso', '62', TO_DATE('08/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_PEN_SLICE
(FULL_NAME, ASSIGNMENT_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('ABU EL HASSAN, Mr. Hashim', '63', TO_DATE('06/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

December 14, 2012 - 2:04 pm UTC

how about a create table?

does this example you posted "get stuck"???

have you traced this?

getting the same issue as per the last comment

lokanath, December 01, 2013 - 4:29 am UTC

I am getting the same issue as per the last person. If it has more than 3 values it is getting stuck without any result.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

perfect solution

getting the same issue as per the last comment

More to Explore

PL/SQL demos

PL/SQL docs

How to split date range into months with start and end dates

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

perfect solution

getting the same issue as per the last comment

More to Explore

PL/SQL demos

PL/SQL docs