Nth occurrence of a weekday in a month and year

-- =============================================
-- Author:<Author,,Rajneesh Kumar>
-- Create date: <Create Date,1 Sept,2009>
-- Description:<Description, To find date at nth occurrence  of a weekday in a month and year>
-- SELECT dbo.getNthOccuranceOfWeekDay(2009,10,3,1)
-- =============================================
/*
@weekday Can be 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thrusday, 6=Friday, 7=Saturday
@iOccAt 1=First,2=Second,3=Third,4=Fourth,>4=Last Occurrence 
*/CREATE FUNCTION dbo.getNthOccuranceOfWeekDay
(
@year INT,
@month TINYINT,
@weekday TINYINT, 
@iOccAt TINYINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME,@retDate DATETIME

SET @dt=CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

IF(MONTH(DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))=@month)
BEGIN 
IF(MONTH(DATEADD(WEEK,@iOccAt-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SET @retDate = DATEADD(WEEK,@iOccAt-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))
ELSE
SET @retDate = DATEADD(WEEK,@iOccAt-2,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@iOccAt,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SET @retDate = DATEADD(WEEK,@iOccAt,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))
ELSE
SET @retDate = DATEADD(WEEK,@iOccAt-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))
END
RETURN @retDate 
END
GO