September 1, 2009 at 3:35 pm
Comments posted to this topic are about the item Nth occurrence of a weekday in a month and year
November 27, 2009 at 3:36 am
IF OBject_ID('getNthOccuranceOfWeekDay') IS NOT NULL
BEGIN
DROP FUNCTION dbo.getNthOccuranceOfWeekDay
END
GO
CREATE FUNCTION dbo.getNthOccuranceOfWeekDay
(
@year VARCHAR(4),
@month TINYINT,
@weekday TINYINT,
@iOccAt TINYINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME, @dtNextMonth DATETIME,@retDate DATETIME,@WkTINYINT
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)> @WeekdayTHEN DATEADD(dd, 7*@iOccAt, DATEADD(dd, @weekday - DATEPART(DW,@dt)+ 7 , @dt))
WHEN DATEPART(DW,@dt)< @WeekdayTHEN DATEADD(dd, 7*@iOccAt,DATEADD(dd, @weekday - DATEPART(DW,@dt) , @dt) )
ELSE @dt
END
RETURN @dt
END
GO
Regards,
Mitesh OSwal
+918698619998
April 18, 2010 at 4:32 pm
Working great for 1,2,3 & 4th occurances, but if i pass in 5 for april 2010, i am getting the wrong date
For 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....
April 7, 2014 at 5:58 am
DECLARE @year VARCHAR(4),
@month TINYINT,
@weekday TINYINT,
@iOccAt TINYINT
DECLARE @dt DATETIME
SELECT @iOccAt = 5,@weekday =6,@month = 5,@year = 2014
select@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
May 24, 2016 at 6:43 am
Thanks for the script. I like date sql.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply