Solved

Generate list of dates in between a date range

Posted on 2009-04-10
15
1,336 Views
Last Modified: 2013-12-07
I need to generate a list of dates in between two date ranges with an increment of 15 days in a select query. for e.g if start date = 01-jan-2009 and end date is = 31-mar-2009
I want the result like the following:
date 1               date 2

--------------------------------

01-jan-2009    15-jan-2009

16-jan-2009    31-jan-2009

01-feb-2009    15-feb-2009

16-feb-2009    28-feb-2009

01-mar-2009    15-mar-2009

16-mar-2009    31-mar-2009

Open in new window

0
Comment
Question by:farhanitman
15 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 24115092
it is a typical loop problem.
Oracle SQL doesn't support loops.
Use PL/SQL instead.
0
 

Author Comment

by:farhanitman
ID: 24115106
How to do it in a PL SQL select query?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24115193
actually you can generate your list with just sql...

however your dates aren't incrementing by 15 days every time

01-jan-2009    15-jan-2009   inc=14 days
16-jan-2009    31-jan-2009   inc=15 days
01-feb-2009    15-feb-2009   inc=14 days
16-feb-2009    28-feb-2009   inc=12 days
01-mar-2009    15-mar-2009 inc=14 days
16-mar-2009    31-mar-2009 inc=15 days

looks like you are "really" looking for 1st of month-15th of month and 16th of month to end of month for all months in your range.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24115249
to produce the posted output...
with 1st and 16th dayte1 and  15th and end-of-month date2

try this...
    SELECT   CASE

                 WHEN MOD(LEVEL, 2) = 1 THEN

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR(LEVEL / 2))

                 ELSE

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 15, FLOOR((LEVEL - 1) / 2))

             END

                 date1,

             CASE

                 WHEN MOD(LEVEL, 2) = 1 THEN

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 14, FLOOR(LEVEL / 2))

                 ELSE

                     LAST_DAY(

                         ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR((LEVEL - 1) / 2))

                     )

             END

                 date2

      FROM   DUAL

CONNECT BY   LEVEL <=

                 (MONTHS_BETWEEN(TO_DATE('31-mar-2009', 'dd-mon-yyyy'),

                                TO_DATE('01-jan-2009', 'dd-mon-yyyy'))

                 * 2)

                 + 1

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24115314
this might be a more natural way of evaluating the end-condition
    SELECT   CASE

                 WHEN MOD(LEVEL, 2) = 1 THEN

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR(LEVEL / 2))

                 ELSE

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 15, FLOOR((LEVEL - 1) / 2))

             END

                 start_date,

             CASE

                 WHEN MOD(LEVEL, 2) = 1 THEN

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 14, FLOOR(LEVEL / 2))

                 ELSE

                     LAST_DAY(

                         ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR((LEVEL - 1) / 2))

                     )

             END

                 end_date

      FROM   DUAL

CONNECT BY   LAST_DAY(ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR((LEVEL - 1) / 2))) <=

                 TO_DATE('31-mar-2009', 'dd-mon-yyyy')

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24115333
the connect by from dual method only works in 9i and above.
If you have 8i then I suggest using...

change dba_objects to some table with enough rows to satisfy the range.
You're not really going to query any data from the table, but you need something to count rows on
SELECT   CASE

             WHEN MOD(ROWNUM, 2) = 1 THEN

                 ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR(ROWNUM / 2))

             ELSE

                 ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 15, FLOOR((ROWNUM - 1) / 2))

         END

             start_date,

         CASE

             WHEN MOD(ROWNUM, 2) = 1 THEN

                 ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy') + 14, FLOOR(ROWNUM / 2))

             ELSE

                 LAST_DAY(

                     ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR((ROWNUM - 1) / 2))

                 )

         END

             end_date

  FROM   dba_objects -- use any large table here

 WHERE   LAST_DAY(ADD_MONTHS(TO_DATE('01-jan-2009', 'dd-mon-yyyy'), FLOOR((ROWNUM - 1) / 2))) <=

             TO_DATE('31-mar-2009', 'dd-mon-yyyy')

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 24118808
See attached.
date-range.txt
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:farhanitman
ID: 24120814
In reply tp sdstuber, i tried your query and it works fine but the dates need not necessarily be first-mid-end of month dates, it can be any range, but if the date range is more than 15 days then i will break the range into 15 days period.
So if the date range is 1-jan-2009 to 20-jan-2009
then result should be in the following:
Also I have oracle 10g.

01-jan-2009    15-jan-2009 

16-jan-2009    20-jan-2009 

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24120892
what if your range was 5-jan-2009 to 18-mar-2009?  what would you expect the ranges to be?
0
 

Author Comment

by:farhanitman
ID: 24120947
15 days gap between dates at most possible



05-jan-2009   20-jan-2009 -- 15 DAYS GAP

21-jan-2009  05-feb-2009 -- 15 DAYS GAP

06-feb-2009  21-feb-2009 -- 15 DAYS GAP

22-FEB-2009  09-MAR-2009 -- 15 DAYS GAP

09-MAR-2009  18-MAR-2009 -- remaining 9 days 

Open in new window

0
 

Author Comment

by:farhanitman
ID: 24120950
start and end should always be inclusive in the date ranges specified by the user
0
 

Author Comment

by:farhanitman
ID: 24120954
if the gap between date ranges is less than 15 days then i will just use the actual range, otherwise break the range into 15 days periods
0
 

Author Comment

by:farhanitman
ID: 24120957
there was an error in the last date so here it is again...
5-jan-2009   20-jan-2009 -- 15 DAYS GAP

21-jan-2009  05-feb-2009 -- 15 DAYS GAP

06-feb-2009  21-feb-2009 -- 15 DAYS GAP

22-FEB-2009  09-MAR-2009 -- 15 DAYS GAP

10-MAR-2009  18-MAR-2009 -- remaining 8 days

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 25 total points
ID: 24121861
since you're going with consistent 15 day gaps, your original lists must not have been correct... that makes the query much easier

    SELECT   TO_DATE('05-jan-2009', 'dd-mon-yyyy') + ((LEVEL - 1) * 16),
             LEAST(TO_DATE('05-jan-2009', 'dd-mon-yyyy') + ((LEVEL - 1) * 16) + 15,
                   TO_DATE('18-mar-2009', 'dd-mon-yyyy'))
      FROM   DUAL
CONNECT BY   TO_DATE('05-jan-2009', 'dd-mon-yyyy') + ((LEVEL - 1) * 16) <=
                 TO_DATE('18-mar-2009', 'dd-mon-yyyy')
0
 

Author Closing Comment

by:farhanitman
ID: 31568874
Thanks :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now