CODEWITHSUNDEEP
oracle-database
Jonah
Jonah

Reputation: 13

Oracle: Last Friday of the previous month

    SELECT 
      TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE)-7, 'FRIDAY'),
                       ' DD MONTH, YYYY') "LAST_FRIDAY"
    FROM DUAL;

this will give me current month last Friday . I need a previous month last Friday.

Upvotes: 0

Views: 810

Answers (2)

Moita
Moita

Reputation: 11

You can execute the query below.

My logic to that is:

1-Get the last day of previous month

2-Get the day of week from date of item 1

3-Some logic with "case" to get wish date

select case
         when WLD = 7 then LD-1 -- if Saturday, get previous day
         when WLD = 6 then LD   -- if Friday, then ok
         when WLD = 5 then LD-6 -- if Thursday, get 6 days ago
         when WLD = 4 then LD-5 -- if Wednesday, get 5 days ago
         when WLD = 3 then LD-4 -- if Tuesday, get 4 days ago
         when WLD = 2 then LD-3 -- if Monday, get 3 days ago
         when WLD = 1 then LD-2 -- if Sunday, get 2 days ago
       end as LAST_FRIDAY_LAST_MONTH -- HERE IS THE LAST FRIDAY OF MONTH
  from (          
        select last_day(add_months(trunc(sysdate),-1))               as LD   -- last day of previous month
             , to_char(last_day(add_months(trunc(sysdate),-1)),'D')  as WLD  -- week day of last day of previous month
          from dual
       )

Upvotes: 1

user8406805
user8406805

Reputation:

You are about to close, just you need to use the ADD_MONTH as below:

1. Compare to Today.

  SELECT 
      TO_CHAR(NEXT_DAY(LAST_DAY(add_months(SYSDATE,-1))-7, 'FRIDAY'),
                       ' DD MONTH, YYYY') "LAST_FRIDAY"
    FROM DUAL;

2. To get static last day

select dt
from(
SELECT to_char(last_day(add_months(sysdate,-1)),'DD MONTH, YYYY') dt,
        to_char(last_day(add_months(sysdate,-1))-level,'DAY') dy
  FROM DUAL
  connect by rownum <= 7
) where trim(dy) = 'FRIDAY'

Upvotes: 2

Related Questions