## Nth occurrence of a weekday in a month and year

 Author Message rajneeshkumardev SSC Rookie Group: General Forum Members Points: 42 Visits: 16 Comments posted to this topic are about the item Nth occurrence of a weekday in a month and year Mitesh Oswal SSCrazy Group: General Forum Members Points: 2282 Visits: 653 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 jordonpilling Ten Centuries Group: General Forum Members Points: 1325 Visits: 464 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.... Mitesh Oswal SSCrazy Group: General Forum Members Points: 2282 Visits: 653 DECLARE @year VARCHAR(4),@month TINYINT,@weekday TINYINT, @iOccAt TINYINTDECLARE @dt DATETIMESELECT @iOccAt = 5,@weekday =6,@month = 5,@year = 2014select @dt= DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0))+ CASE WHEN DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0))) > @weekday THEN 7-DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0)))+@weekday ELSE @weekday - (DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0)))) END + (7*(@iOccAt-1)) select @dt, DATENAME(Dw,@dt) Regards,Mitesh OSwal+918698619998 Iwas Bornready SSC Guru Group: General Forum Members Points: 59336 Visits: 886 Thanks for the script. I like date sql.