Query to find the dates of all the saturdays in this yr
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query to find the dates of all the saturdays in this yr

  1. #1
    Join Date
    May 2001
    Posts
    1

    Question

    can any1 pls help me with the query that will give me the DATES of all the saturdays in this year.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Lightbulb 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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Question 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
  •  



Click Here to Expand Forum to Full Width