I am trying to retrieve data for the previous week. Using the code below, I get the previous week starting on Sunday and ending on Saturday. 

WHERE TO_CHAR(OUTAGE_END_TIME,'WW') 
    = TO_CHAR(SYSDATE,'WW') - 1 
  AND TO_CHAR(OUTAGE_END_TIME,'YY') 
    = TO_CHAR(SYSDATE,'YY')

How can I retrieve the data from Monday to Sunday?

Also, is there a place I can go to retrieve all of the different functions using dates for both an Oracle and SQL Server database? Thanks in advance for any help you can offer!

    Requires Free Membership to View

    Login

First of all, let me preface this by saying that I don't have an Oracle system to test this on, so I cannot say for sure that it will work. Date functions are notoriously slippery from one DBMS to the next, and Oracle's are particularly wonky. 

where outage_end_time >= 
   case when to_char(sysdate,'DAY')='SUN'
        then trunc(sysdate-13,'DDD')
        else next_day(sysdate,'SUN')-13
    end   
  and outage_end_time < 
   case when to_char(sysdate,'DAY')='SUN'
        then trunc(sysdate-6,'DDD')
        else next_day(sysdate,'SUN')-6
    end

If today isn't Sunday, move ahead to the next Sunday. Then the range of days from 13 days ago to 7 days ago is the range for the previous week. Notice that I used LESS THAN 6 days ago for the upper end of the range, to allow for values where the time portion of the column value is within the 7th day.

For a comparison of Oracle and SQL Server, you may try these:

This was first published in May 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top