previous friday of month

Question

I am trying to write a query that will return the last friday of the previous month This is what I have so far.

SELECT sysdate
FROM   DUAL
where (LAST_DAY( SYSDATE ) - INTERVAL '7' DAY,
         'Friday' );

user_10676716 · Answer

Consider:

select next_day(trunc(sysdate, 'month') - interval '8' day, 'Friday') from dual

Rationale:

trunc(sysdate, 'month') gives you the first day of the current month
we offset that value by 8 days so we can the the date 7 days before the end of the previous month
then, next_day() returns the next Friday after this date, that is the last Friday of the month

user_10234902 · Answer

Try this:

SELECT NEXT_DAY( LAST_DAY( TO_DATE('01-' || TO_CHAR(SYSDATE, 'MON-YYYY')) - 1 ) - INTERVAL '7' DAY, 'FRIDAY' ) FROM DUAL;