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

 Nth occurrence of a weekday in a month and year Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 01, 2009 3:35 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, January 18, 2011 8:54 AM Points: 32, Visits: 8
 Comments posted to this topic are about the item Nth occurrence of a weekday in a month and year
Post #781124
 Posted Friday, November 27, 2009 3:36 AM
 Right there with Babe Group: General Forum Members Last Login: Today @ 9:41 PM Points: 751, Visits: 584
 IF OBject_ID('getNthOccuranceOfWeekDay') IS NOT NULLBEGIN DROP FUNCTION dbo.getNthOccuranceOfWeekDayENDGOCREATE FUNCTION dbo.getNthOccuranceOfWeekDay( @year VARCHAR(4), @month TINYINT, @weekday TINYINT, @iOccAt TINYINT)RETURNS DATETIMEASBEGIN DECLARE @dt DATETIME, @dtNextMonth DATETIME,@retDate DATETIME,@Wk TINYINT SELECT @dt = DATEADD(mm,@month-1,@year),--CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01' @dtNextMonth = DATEADD(mm,@month,@year), @Wk = CASE WHEN DATEPART(DW,@dt ) IN (7,1) THEN DATEDIFF(WK,@dt,@dtNextMonth) ELSE DATEDIFF(WK,@dt,@dtNextMonth) -1 END SELECT @iOccAt = CASE WHEN @iOccAt > @wk THEN @wk-1 ELSE @iOccAt-1 END SELECT @dt = CASE WHEN DATEPART(DW,@dt)> @Weekday THEN DATEADD(dd, 7*@iOccAt, DATEADD(dd, @weekday - DATEPART(DW,@dt)+ 7 , @dt)) WHEN DATEPART(DW,@dt)< @Weekday THEN DATEADD(dd, 7*@iOccAt,DATEADD(dd, @weekday - DATEPART(DW,@dt) , @dt) ) ELSE @dt END RETURN @dtENDGO Regards,Mitesh OSwal+918698619998
Post #825528
 Posted Sunday, April 18, 2010 4:32 PM
 Valued Member Group: General Forum Members Last Login: Friday, March 07, 2014 2:41 AM Points: 50, Visits: 396
 Working great for 1,2,3 & 4th occurances, but if i pass in 5 for april 2010, i am getting the wrong dateFor example:`SELECT dbo.getNthOccuranceOfWeekDay(YEAR(GETDATE()), MONTH(GETDATE()), 1, 5)`Resulting in:`2010-04-18 00:00:00.000`Should be:`2010-04-25 00:00:00.000` (because the 25th is the last occurance of sunday in April.Please advise.Edit: my above issue occurs with Mitesh Oswal's version only, the version from Rajneesh Kumardev does not suffer from this problem. Knock Knock, Who's There?, sp underscore, sp underscore who?spid1, spid2, spid3....
Post #905620
 Posted Monday, April 07, 2014 5:58 AM
 Right there with Babe Group: General Forum Members Last Login: Today @ 9:41 PM Points: 751, Visits: 584