## Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year.

See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx

This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate.

CREATE FUNCTION dbo.fnWeekdayOfPeriod

(

@FromDate AS DATETIME,

@ToDate AS DATETIME,

@WeekDay TINYINT,

@Nth INT

)

RETURNS DATETIME

AS

BEGIN

RETURN (

SELECT DATEADD(DAY,

CASE

WHEN @Weekday < theFrom % 7 THEN theFrom - theFrom % 7 + @Weekday + 7

ELSE theFrom - theFrom % 7 + @Weekday

END,

'17530101') AS theDate

FROM (

SELECT CASE SIGN(@Nth)

WHEN 1 THEN DATEDIFF(DAY, '17530101', @FromDate) + 7 * @Nth - 7

ELSE DATEDIFF(DAY, '17530101', @ToDate) + 7 * @Nth + 1

END AS theFrom,

CASE SIGN(@Nth)

WHEN 1 THEN DATEDIFF(DAY, '17530101', @FromDate) + 7 * @Nth - 1

ELSE DATEDIFF(DAY, '17530101', @ToDate) + 7 * @Nth + 7

END AS theTo,

DATEDIFF(DAY, '17530101', @FromDate) AS theMin,

DATEDIFF(DAY, '17530101', @ToDate) AS theMax

WHERE @Nth BETWEEN -430307 AND 430307

AND @Nth <> 0

AND @Weekday BETWEEN 0 AND 7

) AS d

WHERE CASE

WHEN @Weekday < theFrom % 7 THEN theFrom - theFrom % 7 + @Weekday + 7

ELSE theFrom - theFrom % 7 + @Weekday

END BETWEEN theMin AND theMax

)

END

Print | posted on Tuesday, November 03, 2009 4:11 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]