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:
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading