can any1 pls help me with the query that will give me the DATES of all the saturdays in this year.
Printable View
can any1 pls help me with the query that will give me the DATES of all the saturdays in this year.
SELECT NEXT_DAY( TO_DATE( '31-DEC-2000', 'DD-MON-YYYY') + (ROWNUM-1)*7, 'SATURDAY')
FROM user_objects
WHERE ROWNUM <= 52;
If user_objects has less than 52 rows, the query will not work. The best way to get only SATURDAY is to create a dummy table with a single column and enter 53 rows. And then use the table in the SELECT Statement.
You are correct, I should have used ALL_OBJECTS instead of USER_OBJECTS.
No need for extra table, every user have hundreds if not thousands of rows available in ALL_OBJECTS....
When i executed the query as said above i got the correct output .
But when i tried to find for a particular day i gave the query
SELECT NEXT_DAY( sysdate,'sunday') from dual;
I GOT THE CORRECT DAY for the given query so i used the same query (as above)
SELECT NEXT_DAY( TO_DATE( '31-DEC-2000', 'DD-MON-YYYY') + (ROWNUM-1)*7, 'SATURDAY')
FROM user_objects
WHERE ROWNUM <= 52;
and my query looked something like (without using to_date)
SELECT NEXT_DAY( sysdate + (ROWNUM-1)*7, 'SATURDAY')
FROM user_objects
WHERE ROWNUM <= 52;
i got the output in which all the days are 3 days ahead of the previous output.
What could be the reason for this??????????
The sysdate is 07-JUN-01.
Your query correctly lists all saturday-dates for the next 52 weeks on my instance. What do you mean the dates are 3-days ahead of the previous output? Can you post a sample of dates it is returning?
If i query the below statement ...
SELECT NEXT_DAY( TO_DATE( '31-DEC-2000', 'DD-MON-YYYY') + (ROWNUM-1)*7, 'SATURDAY')
FROM user_objects
WHERE ROWNUM <= 52;
I am getting answer as....
NEXT_DAY(
---------
06-JAN-01
13-JAN-01
20-JAN-01
27-JAN-01
03-FEB-01
10-FEB-01
17-FEB-01
24-FEB-01
03-MAR-01
10-MAR-01
17-MAR-01
......
----
If i am using the statement as
SELECT NEXT_DAY( sysdate + (ROWNUM-1)*7, 'SATURDAY')
FROM user_objects
WHERE ROWNUM <= 52;
i am getting result as..
NEXT_DAY(
---------
09-JUN-01
16-JUN-01
23-JUN-01
30-JUN-01
07-JUL-01
14-JUL-01
21-JUL-01
28-JUL-01
04-AUG-01
11-AUG-01
18-AUG-01
...
Later one results is correct.... but why the first query gives wrong resluts...?
Please clarify...