-- Holidays that are designated as first Monday, fourth Thursday etc. can be computed as
-- shown below using the analytic function ROW_NUMBER(). 

-- The third Monday of January is Martin Luther King Day in USA.
-- In 2014, it is January 20th 2014. 

-- Parameters for the query
-- i_yyyymm => 201401
-- i_3ltr_day => Mon (mon or MON should be ok)
-- i_cnt => 3

WITH q_date AS
(SELECT  &i_yyyymm  i_yyyymm FROM  DUAL),
 q_days AS 
(SELECT
   TO_DATE(i_yyyymm,'yyyymm') bom_date,
   LAST_DAY(TO_DATE(i_yyyymm,'yyyymm'))+1-TO_DATE(i_yyyymm,'yyyymm') mth_days
 FROM q_date), 
 q_wk_days AS
(SELECT
   bom_date+level-1 mth_day,
   TO_CHAR(bom_date+level-1,'Dy') wk_day   
 FROM  q_days
 CONNECT BY level <= mth_days),
 q_holiday AS
(SELECT 
   mth_day ref_date,
   ROW_NUMBER() OVER (ORDER BY wk_day) day_cnt
 FROM  q_wk_days
 WHERE  wk_day = INITCAP('&i_3ltr_day'))     -- Mon, Tue, etc.
SELECT 
 TO_CHAR(ref_date,'mm/dd/yyyy') holiday_date
FROM q_holiday
WHERE  day_cnt = &i_cnt;
             


-- Third Monday of February is President's day and it is February 17th, 2014.  Using 
-- the same query, the first Monday of September is Labor Day in USA, which is
-- computed as September 1st, 2014. 


Output: Other Similar Holidays