michelle.baumgarten (8/24/2009)
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
I suspect that you're changing the code above and then trying to run it as if it were a script. It will not work that way. Run the code as it is above to create the function and then run something like the following to use the function...
SELECT dbo.getDateAtNthOccurence(GETDATE(),1,5)
--Jeff Moden
Change is inevitable... Change for the better is not.