Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1360
  • Last Modified:

Generate list of dates in between a date range

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
farhanitman
Asked:
farhanitman
1 Solution
 
schwertnerCommented:
it is a typical loop problem.
Oracle SQL doesn't support loops.
Use PL/SQL instead.
0
 
farhanitmanAuthor Commented:
How to do it in a PL SQL select query?
0
 
sdstuberCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
awking00Commented:
See attached.
date-range.txt
0
 
farhanitmanAuthor Commented:
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
 
sdstuberCommented:
what if your range was 5-jan-2009 to 18-mar-2009?  what would you expect the ranges to be?
0
 
farhanitmanAuthor Commented:
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
 
farhanitmanAuthor Commented:
start and end should always be inclusive in the date ranges specified by the user
0
 
farhanitmanAuthor Commented:
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
 
farhanitmanAuthor Commented:
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
 
sdstuberCommented:
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
 
farhanitmanAuthor Commented:
Thanks :)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now