IF OBject_ID('getNthOccuranceOfWeekDay') IS NOT NULL

BEGIN

DROP FUNCTION dbo.getNthOccuranceOfWeekDay

END

GO

CREATE FUNCTION dbo.getNthOccuranceOfWeekDay

(

@year VARCHAR(4),

@month TINYINT,

@weekday TINYINT,

@iOccAt TINYINT

)

RETURNS DATETIME

AS

BEGIN

DECLARE @dt DATETIME, @dtNextMonth DATETIME,@retDate DATETIME,@WkTINYINT

SELECT

@dt= DATEADD(mm,@month-1,@year),--CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

@dtNextMonth=DATEADD(mm,@month,@year),

@Wk=CASE WHEN DATEPART(DW,@dt ) IN (7,1) THEN DATEDIFF(WK,@dt,@dtNextMonth) ELSE DATEDIFF(WK,@dt,@dtNextMonth) -1 END

SELECT @iOccAt=CASE WHEN @iOccAt > @wk THEN @wk-1 ELSE @iOccAt-1 END

SELECT

@dt=

CASE WHEN DATEPART(DW,@dt)> @WeekdayTHEN DATEADD(dd, 7*@iOccAt, DATEADD(dd, @weekday - DATEPART(DW,@dt)+ 7 , @dt))

WHEN DATEPART(DW,@dt)< @WeekdayTHEN DATEADD(dd, 7*@iOccAt,DATEADD(dd, @weekday - DATEPART(DW,@dt) , @dt) )

ELSE @dt

END

RETURN @dt

END

GO