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.
