Greetings all powerful Stack.
I am sure that this question must have been asked and answered on the site multiple times, however I am struggling to identify any previous answers through the search function. I know this can be done rather easily as I have seen it implemented before, however it was a while ago and I cannot remember the particulars.
What I specifically need to do, is to be able to return the date of the last Friday before the sysdate, so that I can use it as a constraint in the WHERE clause.
To be clear, if I ran the script today (Thursday 29th September), I would want last Friday's date to be returned (Friday 22nd September). If I run the script two days from now (Saturday 1st October), I would want tomorrows date to be returned (Friday 30th September).
Think that should be clear, but please let me know if there is any ambiguity. If it makes a difference, I'm working with an Oracle database
I know this is a pretty rudimentary question with a simple solution, but any input to point me in the right direction would be much appreciated. Thank you kindly in advance :)
Oracle has a convenient function called
next_day() (see here), which does something related. It returns the next day of week after a given date. You can use this to get what you want:
select next_day(trunc(sysdate), 'FRIDAY') - 7 from dual;
Note: If today were a Friday, then this would return today's date. I'm not sure if that is what you intend. If you want the previous Friday, then use
sysdate - 1.