Q
Problem solve Get help with specific problems with your technologies, process and projects.

Monday to Sunday weeks in Oracle

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. How can I retrieve the data from Monday to Sunday?

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!

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 last published in May 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close