We help IT Professionals succeed at work.

generate date sequence

crgary_tx
crgary_tx asked
on
1,699 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



Comment
Watch Question

Commented:
select sysdate + level - 1 from dual connect by level <= 366;
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
True, however we are only talking about 13.  Personally, I have not seen an application yet that uses less than 13 tables.
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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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;
/
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.