Since you asked --- I copied the code, entered '01/MAR/2009',1,3 for the parameters, and got a message saying:

Incorrect syntax near keyword FUNCTION.

A RETURN statement with a RETURN value cannot be used in this context.

CREATE FUNCTION dbo.getDateAtNthOccurence

(

@inputDate DATETIME,

@day TINYINT, -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday

@occurAt TINYINT -- 1:First, 2:Second, 3:Third, 4:Fourth, >4:Last

)

RETURNS DATETIME

AS

BEGIN

DECLARE @counter TINYINT,

@firstDate DATETIME,

@lastDate DATETIME,

@retDate DATETIME

SELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))

WHILE @firstDate<=@lastDate

BEGIN

IF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAt

BEGIN

SELECT @retDate = @firstDate,@counter = @counter+1

END

SET @firstDate = DATEADD(DAY,1,@firstDate)

END

-- Return the result of the function

RETURN @retDate

END

GO