with dateparam as -- Common Table Expression to generate all dates in Current Year
(
SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') = TRUNC(SYSDATE, 'YYYY')
)
select distinct
add_months(last_day(mydate)+1,-1) as firstdayofmonth
,to_char(add_months(last_day(mydate)+1,-1),'DAY') as dayofweek
,case when to_char(add_months(last_day(mydate)+1,-1),'D') = 1
then add_months(last_day(mydate)+1,-1) + 5 -- add five days if first day of month is Sunday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 2
then add_months(last_day(mydate)+1,-1) + 4 -- add four days if first day of month is Monday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 3
then add_months(last_day(mydate)+1,-1) + 3 -- add three days if first day of month is Tuesday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 4
then add_months(last_day(mydate)+1,-1) + 2 -- add two days if first day of month is Wednesday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 5
then add_months(last_day(mydate)+1,-1) + 1 -- add one day if first day of month is Thursday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 7
then add_months(last_day(mydate)+1,-1) + 6 -- add six days if first day of month is Saturday
else add_months(last_day(mydate)+1,-1)
end as firstfridayofmonth
,case when to_char(add_months(last_day(mydate)+1,-1),'D') = 1
then to_char(add_months(last_day(mydate)+1,-1)+5,'DAY') -- add five days if first day of month is Sunday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 2
then to_char(add_months(last_day(mydate)+1,-1)+4,'DAY') -- add four days if first day of month is Monday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 3
then to_char(add_months(last_day(mydate)+1,-1)+3,'DAY') -- add three days if first day of month is Tuesday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 4
then to_char(add_months(last_day(mydate)+1,-1)+2,'DAY') -- add two days if first day of month is Wednesday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 5
then to_char(add_months(last_day(mydate)+1,-1)+1,'DAY') -- add one day if first day of month is Thursday
when to_char(add_months(last_day(mydate)+1,-1),'D') = 7
then to_char(add_months(last_day(mydate)+1,-1)+6,'DAY') -- add six days if first day of month is Saturday
else to_char(add_months(last_day(mydate)+1,-1),'DAY')
end as firstfridayofmonth
from dateparam
order by 1