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:
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments