Generate list of dates using SQL?

tetsujin1979

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

Tom Dunne

Works fine for me on Oracle 10g.

What editor are you using to enter the above SQL?

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

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?