Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nth occurrence of a weekday in a month and year Expand / Collapse
Author
Message
Posted Tuesday, September 1, 2009 3:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 11:25 PM
Points: 32, Visits: 16
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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646
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



Regards,
Mitesh OSwal
+918698619998
Post #825528
Posted Sunday, April 18, 2010 4:32 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 4, 2014 6:09 AM
Points: 50, Visits: 404
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
Posted Monday, April 7, 2014 5:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646
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
Post #1559014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse