Find occurrence of a specific day in a month

I am resuming blogging. Sorry for being away for so long. Many a time, we encounter situations where something is to be executed on a 2nd Saturday, a 4th Tuesday of a month or likewise. It would be so handy to have something that easily verifies the occurrence of a specific weekday in a given month and then executes other TSQL code after such an occurrence has been verified. A reusable code that returns a true or false is much easier than actually going through the dates and counting the occurrence of a specific day. So, I embark on creating a scalar function for this purpose (In this function, I have used CTE, Recursive CTE, IIF, EOMonth):

USE [DB_NAME]
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
  
create function [dbo].[dbf_Is_day_Nth_occurrence]
(@i_Day varchar(10)
,@i_Nth_Occurrence tinyint)

Returns bit
as
/************************************************************************************************************************************************************
 Date      Altered by          Description
__________ ___________________ ___________________________________________________________________________________________________________________________
04/15/2017 Suresh K. Maganti   Created. True or False. is it the 2nd Tuesday of this Month?
************************************************************************************************************************************************************/
begin
   declare @o_Return bit

;with CTE_Base (First_of_Month, Last_of_Month)
as
  (select convert(datetime, convert(varchar(4), year(getdate())) + right('0' + convert(varchar(2), month (getdate())), 2) + '01'), convert(datetime, EOMonth(getdate())) )
--select * from CTE_Base

,CTE_Recursive (The_Date, The_Day, Occurrence_in_Month, Last_of_Month)
as
  (select First_of_Month, datename(WEEKDAY, First_of_Month), 1, Last_of_Month from CTE_Base
   union all
   select The_Date + 1, datename(WEEKDAY, The_Date + 1), Occurrence_in_Month + 1, Last_of_Month from CTE_Recursive
   where The_Date < Last_of_Month)

,CTE_Final (The_Date, The_Day, Nth_Occurrence_of_Day)
as
  (select The_Date, The_Day, Row_Number() over(partition by The_Day order by Occurrence_In_Month)
   from CTE_Recursive)

  select @o_Return = iif ( (The_Day = @i_Day) and (Nth_Occurrence_of_Day = @i_Nth_Occurrence), 1, 0)
  from CTE_Final
  where convert(varchar, The_Date, 112) = convert(varchar, getdate(), 112)

  return @o_Return
end
go


Today, 05/30/2017 is 5th Tuesday of May 2017. Now, let me check using the function if today is the 1st Sunday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Sunday', 1)
I get a 0. The answer is No.

Let me try to see if it is the 5th Tuesday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5)
I get a 1. The answer is Yes.

So, I can do the next set of things that I can do for today as follows:

if [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5) = 1
begin
   Print 'The next article would be a bigger one.'
end
go