generate date sequence

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



LVL 13
crgary_txAsked:
Who is Participating?
 
konektorCommented:
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
 
konektorCommented:
select sysdate + level - 1 from dual connect by level <= 366;
0
 
johnsoneSenior Oracle DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
konektorCommented:
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
 
johnsoneSenior Oracle DBACommented:
True, however we are only talking about 13.  Personally, I have not seen an application yet that uses less than 13 tables.
0
 
Jinesh KamdarCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.