Find out particular occurance of weekday in a month

The following function returns the Nth occurance of week day in a month

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =’GetNthWeekday’ AND ROUTINE_SCHEMA = ‘dbo’ AND ROUTINE_TYPE=’FUNCTION’)
DROP FUNCTION dbo.GetNthWeekday
GO
CREATE FUNCTION dbo.GetNthWeekday
(
@date datetime,–Any date of month under consideration
@N int,– Nth occurance
@WeekDay int,–Which week day to look for (Sunday-1,Monday-2,..,Saturday-7)
–returns NULL when calculated date is out of scope of month
@TimeOffset datetime = ’00:00′
)
RETURNS datetime
AS
BEGIN
DECLARE @StartDate datetime,@ResultDate datetime

SET @StartDate=DATEADD(mm,DATEDIFF(mm,0,@date),@TimeOffset)

SELECT @ResultDate=DATEADD(
        dd,
        CASE WHEN DATEPART(DW,DATEADD(wk,@N-1,@StartDate)) > @WeekDay
         THEN (7 – (DATEPART(DW,DATEADD(wk,@N-1,@StartDate))- @WeekDay))
         ELSE (@WeekDay – DATEPART(DW,DATEADD(wk,@N-1,@StartDate)))
        END,
        DATEADD(wk,@N-1,@StartDate)
)
SET @ResultDate= CASE WHEN MONTH(@ResultDate) <> MONTH(@date)
      THEN NULL
      ELSE @ResultDate
     END
RETURN @ResultDate
END

Published by dataplatformcentral