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

x
?
Solved

generate date sequence

Posted on 2007-10-05
8
Medium Priority
?
1,679 Views
Last Modified: 2008-01-09
I need help writing a sql to generate the date containing the current date and one day before and one day after for 12 months. For eg if todays date is 10-5-2007 i need a sequence something like this:

10-5-2007
10-6-2007
10-4-2007
9-5-2007
9-6-2007
9-4-2007
8-5-2007
8-6-2007
8-4-2007
..
..
..
10-5-2006
10-6-2006
10-5-2006



0
Comment
Question by:crgary_tx
8 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 20021350
select sysdate + level - 1 from dual connect by level <= 366;
0
 
LVL 9

Accepted Solution

by:
konektor earned 1000 total points
ID: 20021400
select * from (
select add_months(sysdate,level-12) from dual connect by level <=12
union all
select add_months(sysdate + 1,level-12) from dual connect by level <=12
union all
select add_months(sysdate -1,level-12) from dual connect by level <=12
) order by 1 desc
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1000 total points
ID: 20021424
Konektor, your query only seems to return 1 row:

SQL> select sysdate + level - 1 from dual connect by level <= 366;

SYSDATE+LEVEL-1
--------------------
05-OCT-2007 08:43:20



Kind of the brute force method, but it will work:

SQL> select add_months(trunc(sysdate), (b.r - 1) * -1) dt
  2  from dual,
  3  (select rownum r from all_tables where rownum < 14) b
  4  union all
  5  select add_months(trunc(sysdate) + 1, (b.r - 1) * -1) dt
  6  from dual,
  7  (select rownum r from all_tables where rownum < 14) b
  8  union all
  9  select add_months(trunc(sysdate) - 1, (b.r - 1) * -1) dt
 10  from dual,
 11  (select rownum r from all_tables where rownum < 14) b
 12  order by 1 desc;

DT
--------------------
06-OCT-2007 00:00:00
05-OCT-2007 00:00:00
04-OCT-2007 00:00:00
06-SEP-2007 00:00:00
05-SEP-2007 00:00:00
04-SEP-2007 00:00:00
06-AUG-2007 00:00:00
05-AUG-2007 00:00:00
04-AUG-2007 00:00:00
06-JUL-2007 00:00:00
05-JUL-2007 00:00:00
04-JUL-2007 00:00:00
06-JUN-2007 00:00:00
05-JUN-2007 00:00:00
04-JUN-2007 00:00:00
06-MAY-2007 00:00:00
05-MAY-2007 00:00:00
04-MAY-2007 00:00:00
06-APR-2007 00:00:00
05-APR-2007 00:00:00
04-APR-2007 00:00:00
06-MAR-2007 00:00:00
05-MAR-2007 00:00:00
04-MAR-2007 00:00:00
06-FEB-2007 00:00:00
05-FEB-2007 00:00:00
04-FEB-2007 00:00:00
06-JAN-2007 00:00:00
05-JAN-2007 00:00:00
04-JAN-2007 00:00:00
06-DEC-2006 00:00:00
05-DEC-2006 00:00:00
04-DEC-2006 00:00:00
06-NOV-2006 00:00:00
05-NOV-2006 00:00:00
04-NOV-2006 00:00:00
06-OCT-2006 00:00:00
05-OCT-2006 00:00:00
04-OCT-2006 00:00:00
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:konektor
ID: 20021632
better to use generator of numbers

select level from dual connect by level <= "number of rows you need"

if you use all_tables (or any other system view) and your user doesn't have acces to any table, eg. only to some procedures, generator

select rownum from all_tables where rownum <= "number of rows you need"

will not work
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20021713
True, however we are only talking about 13.  Personally, I have not seen an application yet that uses less than 13 tables.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20021846
johnsone - With due respect, I dont think the issue here is about 13 tables. I believe konektor is right in saying that there maybe users who might not have access to as many tables even though the app. itself might have as many tables. Also, it would make more sense to use a global table like DUAL instead of relying on data dictionary tables since IMO they may be slow when the no. of months reqd. increases from 12 to a higher no.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20022985
Agreed that it may not be the best way, however all users have access to certain things, regardless of application permissions.

I just created a user with no permissions in a 9i database.  While ALL_TABLES only had a few rows in it, ALL_OBJECTS contains 2761 rows, none of which are objects that are part of the application.

The difference between a query on ALL_OBJECTS for 2,760 rows and DUAL for 2,760 rows is 0.3 seconds.
0
 
LVL 32

Expert Comment

by:awking00
ID: 20024274
You might consider writing a procedure -
create procedure make_dt_seq is
begin
for i in 0..12
loop
 for n in -1..1
 loop
 dbms_output.put_line(add_months(sysdate,-i) + n);
 commit;
 end loop;
end loop;
end;
/
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

564 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