Nth occurrence of a weekday in a month and year

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rajneesh Kumar » Nth occurrence of a weekday in a month and...

Nth occurrence of a weekday in a month and year

Rate Topic

Display Mode

Topic Options

Author

Message

rajneeshkumardev

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

Mitesh Oswal

Posted Friday, November 27, 2009 3:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 1:56 AM
Points: 593, Visits: 371

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,@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 @dt
END
GO

Post #825528

jordonpilling

Posted Sunday, April 18, 2010 4:32 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:08 AM
Points: 49, Visits: 374

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....

Post #905620

« Prev Topic | Next Topic »

Permissions