Second to last Friday in Month

I was tasked with creating a SQL Agent Job that would run on the 2nd to last Friday every month; but how to do that? The 3rd Friday of every month isn't necessarily the 2nd to last Friday...The Job Scheduler has an option for "LAST" x of the month, but not 2nd to last, so after some thought I decided to write a function that would do it for me. Now, I'm happy to see any comments from people on a better way of doing this, but this is what I came up with, hope it helps you, or spawns some ideas for you.

Before you can implment the main function, you'll need to have a function that returns the first day of a given month, and the last day of a given month....Ok, you probably don't need them as functions, but I like them that way...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/12/2010
-- Description: Returns the Last day of the month
-- Usage: SELECT dbo.LastOfMonth(GetDate())
-- =====================================================================
CREATE FUNCTION [dbo].[LastOfMonth] 
(
 -- Add the parameters for the function here
 @DTC DATE -- DateToCheck
)
RETURNS DATE
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result DATE

 -- Add the T-SQL statements to compute the return value here
 SELECT @Result = CAST(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@DTC)+1, 0)) AS DATE)

 -- Return the result of the function
 RETURN @Result

END




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/12/2010
-- Description: Returns the first day of the month
-- Usage: SELECT dbo.FirstOfMonth(GetDate())
-- =====================================================================
CREATE FUNCTION [dbo].[FirstOfMonth] 
(
 -- Add the parameters for the function here
 @DTC DATE -- DateToCheck
)
RETURNS DATE
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result DATE

 -- Add the T-SQL statements to compute the return value here
 SELECT @Result = CAST(DATEADD(ms,0,DATEADD(mm,DATEDIFF(m,0,@DTC), 0)) AS DATE)

 -- Return the result of the function
 RETURN @Result

END

And now for the main attraction:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/06/2011
-- Description: Returns the Nth last  of the given month
-- Usage: SELECT dbo.NthLastDoWOfMonth(GetDate(),6,1)
-- =====================================================================
CREATE FUNCTION [dbo].[NthLastDoWOfMonth] 
(
 -- Add the parameters for the function here
 @MonthToCheck DATE  -- MonthToCheck
 ,@DayOfWeek INT -- 1=Sunday...7=Saturday
 ,@WeeksFromLast INT-- 0=Last @DayOfWeek of the month, 1=2nd to last @DayOfWeek of the month, etc.
)
RETURNS DATE
AS
BEGIN
 DECLARE @DaysInMonth TABLE (DayOfMonth DATE, DayOfWeek INT)

 DECLARE @FirstOfMonth DATE
   ,@LastOfMonth DATE
   ,@ThisDay DATE
   ,@Result DATE

 SELECT @FirstOfMonth = dbo.FirstOfMonth(@MonthToCheck), @LastOfMonth = dbo.LastOfMonth(@MonthToCheck)
 SET @ThisDay = @FirstOfMonth
 WHILE @ThisDay <= @LastOfMonth
 BEGIN
  INSERT INTO @DaysInMonth
  SELECT @ThisDay, DATEPART(dw,@ThisDay)
  SET @ThisDay = DATEADD(d,1,@ThisDay)
 END

 ;WITH DaysCTE AS (
  SELECT DayOfMonth ,ROW_NUMBER() OVER(ORDER BY DayOfMonth) AS 'RowNumber'
  FROM @DaysInMonth DIM 
  WHERE DayOfWeek = @DayOfWeek 
 )
 , MaxRow AS (
  SELECT MAX(RowNumber) AS MaxRowNbr
  FROM DaysCTE
 )
 SELECT @Result = DaysCTE.DayOfMonth
 FROM DaysCTE, MaxRow
 WHERE DaysCTE.RowNumber = MaxRow.MaxRowNbr-@WeeksFromLast

 RETURN @Result

END

For some reason I can't figure out, there seems to be a /day tag at the end of the scirpt...it's not in my code, it appears to be in the exact spot as the code stating the end of my SQL...not sure, ignore it.