Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Nth occurence of a day in a month/year Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 13, 2009 4:54 PM
 Grasshopper Group: General Forum Members Last Login: Monday, September 10, 2012 4:37 AM Points: 16, Visits: 30
 Comments posted to this topic are about the item Nth occurence of a day in a month/year
Post #770564
 Posted Friday, August 21, 2009 7:40 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 13, 2011 8:06 AM Points: 2, Visits: 20
 Can't get this to work.
Post #775056
 Posted Friday, August 21, 2009 11:52 AM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 9:59 PM Points: 1,177, Visits: 1,531
 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 ) )`
Post #775298
 Posted Friday, August 21, 2009 9:36 PM
 Grasshopper Group: General Forum Members Last Login: Monday, September 10, 2012 4:37 AM Points: 16, Visits: 30
 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 :)
Post #775498
 Posted Saturday, August 22, 2009 2:00 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:45 PM Points: 34,540, Visits: 28,709
 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 Moden"RBAR 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #775599
 Posted Saturday, August 22, 2009 2:08 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:45 PM Points: 34,540, Visits: 28,709
 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 Moden"RBAR 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #775602
 Posted Monday, August 24, 2009 9:23 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 13, 2011 8:06 AM Points: 2, Visits: 20
 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
Post #776096
 Posted Monday, August 24, 2009 10:26 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:45 PM Points: 34,540, Visits: 28,709
 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 Moden"RBAR 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #776149

 Permissions