Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Generate list of dates using SQL?

Options
  • 10-05-2007 4:38pm
    #1
    tetsujin1979
    Registered Users Posts: 495 ✭✭


    I've been trying to use the SQL in this blog post - http://blogs.conchango.com/jamespipe/archive/2007/01/15/Oracle-SQL-_2D00_-Generate-a-list-of-dates.aspx - to generate a list of dates in Oracle, but no matter what start date I pick, it only lists the next 10 dates, can someone point out to me where I'm going wrong with this?

    select
    to_date('1 2007','MM YYYY')-1 + level as DateRange
    from
    dual
    where
    (to_date('1 2007','MM YYYY')-1+level) <= sysdate
    connect by level<=31

    In theory it should list all the days in January, but instead all I get is:
    2007-01-01
    2007-01-02
    2007-01-03
    2007-01-04
    2007-01-05
    2007-01-06
    2007-01-07
    2007-01-08
    2007-01-09
    2007-01-10


Comments

  • Options
    #2
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    Works fine for me on Oracle 10g.

    What editor are you using to enter the above SQL?


  • Options
    #3
    tetsujin1979
    Registered Users Posts: 495 ✭✭tetsujin1979


    tom dunne wrote:
    Works fine for me on Oracle 10g.

    What editor are you using to enter the above SQL?
    That could be it actually, I'm using SQuirreL - http://squirrel-sql.sourceforge.net/ - to connect to the Oracle DB.
    I work with SQL Server, Oracle and DB2 fairly regularly so it's easier than having all the clients on my machine.
    There's an SQL Server implementation in the comments here - http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx - which works fine in Squirrel, all the days show up correctly
    Thanks, I'll try it in SQL Plus


  • Options
    #4
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    What I was actually getting is that a lot of editors only show you the first X number of records (10 in some cases) - you have to hit a button to show all records (this catches me out on occasion).

    I wonder is it as simple as that?


Advertisement