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