Simple select... 1

[gold2000]

How I write Select command, that result are all days in this year. I would like use this select as subquery in IN/OUT cursor, therefore I don't want use For..loop. Thanks for answer on my stupid question.

Example:

Day
1/1/00
1/2/00
...
12/31/00

 

[MikeLacey]

could you use a WHERE clause like:

WHERE datefield BETWEEN '01-Jan-2000' AND '31-Dec-2000'?

 

[gold2000]

Thank you for replaying me, but your advice isn't answer to my question.
I didn't inquire about condition WHERE, but I'd like to the SELECT return 365 rows, but not only one.

 

[Stargazer77]

You use the Where with the Selet to filter out the 365 days........

 

[nw2394]

This is, I believe, an age old question for all RDBMSs. And to which there is no good answer. THe two strategies that work are:

a) Generate what you want in a for loop and
b) Pre-load a table with the dates you need.

There is no other worthwhile solution.

 

[carp]

It's a little klugey, but you can do something like:

SELECT '31-DEC-99' + the_row
FROM (SELECT rownum the_row
FROM dba_objects
WHERE rownum < 366);

This will give you all of the dates in the year 2000. Of course, if you run into a leap year, you will have to change 366 to 367.

But like Nick said, there aren't any pretty ways to do this.

 

[karluk]

That is an excellent suggestion, Carp. The only thing kludgy about it is the artificial reference to an arbitrary table you know will contain enough rows. It think with a couple changes it will produce a complete answer to Gold2000's question. Try the following:

SELECT trunc(sysdate,'yyyy') - 1 + the_row
FROM (SELECT rownum the_row
FROM dba_objects
WHERE rownum < 367)
where trunc(trunc(sysdate,'yyyy')-1+the_row,'yyyy')
<= sysdate