CODEWITHSUNDEEP
sqloracle-database
flwr_pwr
flwr_pwr

Reputation: 170

ORACLE Find Date ('DD-MON-YYYY') of a given weekday in the past 7 days

i am trying to create a view that compares a SCHEDULE table that has values such as ('Daily', 'Wednesday', 'Tuesday', etc..) and another table (REPORT CREATED) that is updated every day with dates (11-AUG-2017). Basically, if the Schedule table shows Daily, then the Report Created table record value should be whatever sysdate (current date) is equal to. That said, I'm not sure how to find out what the most recent 'Wednesday' or 'Tuesday' is equal to. I did find a function for SQL server (How to get Saturday's Date (Or any other weekday's Date)- SQL Server) ; however, I do not understand how it works and can't find an equivalent in Oracle. Any guidance would be greatly appreciated!

Edit: I have created two sample tables:

Schedules Table:

Report_Name | Frequency
ORDERS_BY_DEPT | Daily
LOW_STOCK | Wednesday
INVENTORY_DISC | Thursday

and the Reports Table:

Report_Name | Create_Dt
INVENTORY_DISC | 3-Aug-2017
LOW_STOCK | 9-Aug-2017
ORDERS_BY_DEPT | 10-Aug-2017

So essentially, the Inventory_Disc report is off, since it should have ran every Thursday but hasn't been updated since last Thursday and the Orders_By_Dept report is off since it is a daily report and didn't run today.

Upvotes: 1

Views: 245

Answers (2)

Andomar
Andomar

Reputation: 238196

You can find the day of the week with:

to_char(sysdate, 'D')

So for example, the last Thursday is:

select  case 
        when to_char(sysdate, 'D') < 4 then sysdate - to_char(sysdate, 'D') - 7 + 4
        else sysdate - to_char(sysdate, 'D') + 4
        end
from    dual

Upvotes: 0

MT0
MT0

Reputation: 168281

Use the NEXT_DAY( date_value, day_string ) function. Take the current day (SYSDATE) and subtract 7 days from it and then find the next day which matches your required day-of-the-week.

So, to get the most recent Wednesday:

SELECT NEXT_DAY( TRUNC( SYSDATE ) - 7, 'WEDNESDAY' )
FROM DUAL

Upvotes: 4

Related Questions