RDG RDG - 10 months ago 81
SQL Question

Oracle SQL - Identify the Friday (or other specific weekday) before sysdate

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 :)

Answer Source

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.