Solved

Generate list of dates in between a date range

Posted on 2009-04-10
15
1,341 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
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.

 
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 32

Expert Comment

by:awking00
ID: 24118808
See attached.
date-range.txt
0
 

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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