Search code examples
sqloracledate

Find the previous Monday's date in Oracle

How do I check that a specific date is a Monday? Then if it is not a Monday, how do I write a SQL script to give me the date of the previous Monday? For example, if the date (27/2/2024) is a Tuesday, how do I make it show me the date of the previous Monday (i.e. 26/02/2024)?

I tried Google searches and watched some YouTube videos but the only solutions I could find were in SQL Server. However, I'm using SQL Developer/Oracle. I've got as far as being able to detect which day of the week a specific date is:

TO_CHAR( TO_DATE( start_date, 'YYYY-MM-DD' ),'DAY' ) AS systart_day_of_week,

Thanks!

Solution

  • I'd suggest the next_day function.

    SQL> select next_day(sysdate - 7, 'MONDAY') from dual;

NEXT_DAY(
---------
26-FEB-24

SQL> select next_day(date '2024-02-16' - 7, 'MONDAY') from dual;

NEXT_DAY(
---------
12-FEB-24

SQL>