oracle finding day of week
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: oracle finding day of week

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    oracle finding day of week

    Is there a function I can use that can find 5 Saturdays ago from the
    current date or find 8 Sundays from the current date?

    What I want to do is pass do something like this:

    select dow(sysdate, -7(saturday) from dual

    dow = Day of week

    Thanks to all who answer.

  2. #2
    Join Date
    Nov 2010
    Posts
    8
    In most solutions they construct a permanent time/date table to handle such problems (Like holidays,working days,day of week,weeknumber,...) with dates from the past to the far future.
    Finding the x-day of type z could be done by ranking futures on the date/time dimension.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    Thanks for the response but I find it hard to believe Oracle does not
    provide that functionalityor some derviation of it.

    Ie

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    14-MAR-11

    SQL> select NEXT_DAY ('01-MAR-11', 'MON') from dual;

    NEXT_DAY(
    ---------
    07-MAR-11

    There has to be some way of doing this? Maybe subtracting 5 weeks from
    the current date and than finding the next occurance of that day?

    Any examples or input would be greatly appreciated.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Try this for seven Sundays in advance counting from next Sunday.
    Code:
    select NEXT_DAY((sysdate + 49), 'SUN') from dual;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    You can also do this:

    Code:
    SYS@rman AS SYSDBA> select to_char(this_date, 'day') day, this_date 
    from ( select trunc(sysdate, 'd')-36 this_date from dual);
    
    DAY          THIS_DATE
    ------------ ---------------
    saturday     05-FEB-11
    
    Elapsed: 00:00:00.10
    SYS@rman AS SYSDBA> select to_char(this_date, 'day') day, this_date 
    from ( select trunc(sysdate, 'd')+41 this_date from dual);
    
    DAY          THIS_DATE
    ------------ ---------------
    saturday     23-APR-11
    
    Elapsed: 00:00:00.05
    this space intentionally left blank

  6. #6
    Join Date
    Jul 2006
    Posts
    195
    Perfect!! This is exactly what I was looking for. Thanks

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