# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function.

CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod
(
@theDate DATETIME,
@theWeekday TINYINT,
@theNth SMALLINT,
@theType CHAR(1)
)
RETURNS DATETIME
BEGIN
RETURN (
SELECT theDate
FROM    (
SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate
FROM    (
SELECT CASE UPPER(@theType)
WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')
WHEN 'Q' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, @theNth, @theDate), '19000101')
WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, @theNth, @theDate), '19000101')
END AS theFirst,
CASE SIGN(@theNth)
WHEN -1 THEN 7 * @theNth
WHEN 1 THEN 7 * @theNth - 7
END AS theDelta
WHERE   @theWeekday BETWEEN 1 AND 7
AND (
@theNth BETWEEN -5 AND 5
AND UPPER(@theType) = 'M'
OR
@theNth BETWEEN -14 AND 14
AND UPPER(@theType) = 'Q'
OR
@theNth BETWEEN -53 AND 53
AND UPPER(@theType) = 'Y'
)
AND @theNth <> 0
) AS d
) AS d
WHERE   CASE UPPER(@theType)
WHEN 'M' THEN DATEDIFF(MONTH, theDate, @theDate)
WHEN 'Q' THEN DATEDIFF(QUARTER, theDate, @theDate)
WHEN 'Y' THEN DATEDIFF(YEAR, theDate, @theDate)
END = 0
)
END

Print | posted on Thursday, June 18, 2009 11:34 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## #re: Extended Get Nth Weekday of period

I used the Extended Get Nth Weekday of period function and it works and fast.
When I using this

Select dbo.fnGetNthWeekdayOfPeriod ('08-January-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

Select dbo.fnGetNthWeekdayOfPeriod ('08-August-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

--- date format dd-mm-yyyy
Select dbo.fnGetNthWeekdayOfPeriod (03-08-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

or this

--- date format mm-dd-yyyy
Select dbo.fnGetNthWeekdayOfPeriod ('08-03-2010', 1, 2, 'Y') AS SecondMondayOfYear2010

all returns the date 2010-01-11 00:00:00.000

I am on SQL Server 2005

on testing with @theType 'M' and 'Q'
the @theDate is in the format of mm-dd-yyyy within single quote
or dd-mmm-yyyy within single quote

8/10/2010 9:05 PM | KC Lee

## #re: Extended Get Nth Weekday of period

update: The comments made last time was my listake. The function works perfectly.
Thanks.
1/3/2011 8:57 PM | KC Lee

## #re: Extended Get Nth Weekday of period

You're welcome.
1/3/2011 9:32 PM | Peso
Comments have been closed on this topic.