## Nth occurence of a day in a month/year

 Author Message Biplov Dan Grasshopper Group: General Forum Members Points: 22 Visits: 32 Comments posted to this topic are about the item Nth occurence of a day in a month/year michelle.baumgarten Forum Newbie Group: General Forum Members Points: 8 Visits: 21 Can't get this to work. sknox SSCertifiable Group: General Forum Members Points: 5603 Visits: 3032 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 monthselect 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 ) )` Biplov Dan Grasshopper Group: General Forum Members Points: 22 Visits: 32 Can't get this to work.How r u setting the parameter? Let me explain it.For example if you need date for 3rd Sunday of March 2009 then use as followsSELECT dbo.getDateAtNthOccurence('01/Mar/2009',1,3)The output will be 2009-03-15 (15th march 2009)The first parameter is any date of month/year for which we want the occurrence date.The second parameter is as follows (if you need sunday then send 1 as parameter value):1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:SaturdayThe third parameter is as follows (if you need 3rd sunday then send 3 as parameter value):1:First, 2:Second, 3:Third, 4:Fourth, >4:Lasthope this will help you to get the output.cheers Jeff Moden SSC Guru Group: General Forum Members Points: 380357 Visits: 42969 michelle.baumgarten (8/21/2009)Can't get this to work.The code works fine for me. You need to post the exact code that you can't get to work so we can help. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 380357 Visits: 42969 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 monthselect 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 ) )`I agree... the only desireable thing missing from the above code is for the "LAST" desired occurance. For example... for 2012-02-01 looking for Sunday(1) as the last occurance of a Sunday in the month (5), your good code returns the first Sunday of March instead of the last Sunday of February. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs michelle.baumgarten Forum Newbie Group: General Forum Members Points: 8 Visits: 21 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 DATETIMEASBEGINDECLARE @counter TINYINT, @firstDate DATETIME,@lastDate DATETIME,@retDate DATETIMESELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))WHILE @firstDate<=@lastDate BEGINIF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAtBEGINSELECT @retDate = @firstDate,@counter = @counter+1ENDSET @firstDate = DATEADD(DAY,1,@firstDate)END-- Return the result of the functionRETURN @retDateENDGO Jeff Moden SSC Guru Group: General Forum Members Points: 380357 Visits: 42969 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 DATETIMEASBEGINDECLARE @counter TINYINT, @firstDate DATETIME,@lastDate DATETIME,@retDate DATETIMESELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))WHILE @firstDate<=@lastDate BEGINIF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAtBEGINSELECT @retDate = @firstDate,@counter = @counter+1ENDSET @firstDate = DATEADD(DAY,1,@firstDate)END-- Return the result of the functionRETURN @retDateENDGOI 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 ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs