Monday, August 5, 2019

Oracle: Obtaining a date for a given business day in a month.

Oracle SQL to get the date for a given business day in a passed in month, sysdate used for example.

With a as(
SELECT
  trunc(sysdate,'MM')- 1 + level AS day
 ,to_number(to_char(trunc(sysdate,'MM')- 1 + level,'D'))  as weekday
 ,rownum as business_day
FROM
  dual
  where to_char(trunc(sysdate,'MM')- 1 + level,'D') not in (1,7)
  -- add in a check for a holday calendar here
CONNECT BY LEVEL between 1 and EXTRACT(day from last_day(sysdate)) 
)
select a.day  from a where business_day = 5

No comments:

Post a Comment