generate date sequence

Posted on 2007-10-05
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:


Question by:crgary_tx
    LVL 9

    Expert Comment

    select sysdate + level - 1 from dual connect by level <= 366;
    LVL 9

    Accepted Solution

    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
    LVL 34

    Assisted Solution

    Konektor, your query only seems to return 1 row:

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

    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;

    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
    LVL 9

    Expert Comment

    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
    LVL 34

    Expert Comment

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

    Expert Comment

    by:Jinesh Kamdar
    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.
    LVL 34

    Expert Comment

    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.
    LVL 31

    Expert Comment

    You might consider writing a procedure -
    create procedure make_dt_seq is
    for i in 0..12
     for n in -1..1
     dbms_output.put_line(add_months(sysdate,-i) + n);
     end loop;
    end loop;

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now