Can't get this to work.

michelle.baumgarten (8/21/2009)

---

Can't get this to work.

sknox (8/21/2009)

---

Why not just do (using the function definition and parameters (and the @firstDate variable) as given):

set @firstDate = dateadd(dd,1-datepart(dd,@inputdate) ,@inputDate) -- ensures that we start with the first of the month

select 
 dateadd(wk, @occurAt - 1, 				-- adds the additional weeks, if any
  dateadd(dd, @day - datepart(dw, @firstDate)		-- moves to the given day of week 
    + case when @day < datepart(dw, dateadd(dd,1-datepart(dd,@firstdate) ,@firstDate)) then 7 else 0 end, 	 -- ensures we don't go back to the last month
   @firstDate
  )
 )

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

SELECT dbo.getDateAtNthOccurence(GETDATE(),1,5)