-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Difference in to_Date Function...
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.
RP Kumar
You Can Win, if u believe Yourself
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
to_date difference...
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...
RP Kumar
You Can Win, if u believe Yourself
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|