Thinking outside the box

Patron Saint of Lost Yaks
posts - 159, comments - 437, trackbacks - 0
<< Timings of different techniques for finding missing records | Home | How to get the Weekday and Nth from a date >>

How to get the Nth weekday of a month

You call this function with three parameters:

1. Any date of the month in question
2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7
3. The choice of weekday count; a positive number means from the beginning of month and a negative number means from the end of month

If a valid date cannot be calculated, NULL is returned. For an extended version which, besides month, also handles quarter and year, see new blog post here
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx

CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
(
    @theDate DATETIME,
    @theWeekday TINYINT,
    @theNth SMALLINT
)
RETURNS DATETIME
BEGIN
    RETURN  (
                SELECT  theDate
                FROM    (
                            SELECT  DATEADD(DAY, 7 * @theNth - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
                            WHERE   @theWeekday BETWEEN 1 AND 7
                                    AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
                        ) AS d
                WHERE   DATEDIFF(MONTH, theDate, @theDate) = 0
            )
END

Print | posted on Wednesday, June 17, 2009 9:05 PM

Feedback

# re: How to get the Nth weekday of a month

Hi Peter
Steve Kass has written the UDF for doing such things as well

create function dbo.NthWeekDay(
@first datetime, -- First of the month of interest (no time part)
@nth tinyint, -- Which of them - 1st, 2nd, etc.
@dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @nth is too large

declare @result datetime
set @result = @first + 7*(@nth-1)
return @result + (7 + @dow - datepart(weekday,@result))%7
end
go
6/18/2009 6:27 AM | Uri Dimant

# re: How to get the Nth weekday of a month

It's a small and neat function, but doesn't work for me, since the DATEPART(WEEKDAY, @result) displays different value for me depending on the current

SET DATEFIRST

setting. Also my function can calculate the last friday, for example, as well as the first or second tuesday.
6/18/2009 7:14 AM | Peso

# re: How to get the Nth weekday of a month

My function also takes care of computed date is out of scope (month).
6/18/2009 7:16 AM | Peso

# re: How to get the Nth weekday of a month


Hey Peter,

Here is another solution using inline UDFs:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/21/calculating-third-wednesday-of-the-month-with-inline-udfs.aspx
6/22/2009 4:23 PM | Alex Kuznetsov

# re: How to get the Nth weekday of a month

Excellent work. Truly top shelf sir. I'm glad I found this so I didn't have to write it myself. You saved me time.

Now I need to change it a bit so it preserves the time value in the date argument and it will be perfect.
9/30/2009 10:50 PM | Charles

# re: How to get the Nth weekday of a month

Replace

SELECT theDate

with

SELECT theDate + DATEADD(DAY, DATEDIFF(DAY, @theDate, 0), @theDate)
10/1/2009 8:47 AM | Peso

# re: How to get the Nth weekday of a month

Peso, thanks for snippet :)
11/17/2009 11:39 AM | cialis online

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 3 and 6 and type the answer here:

Powered by:

Copyright © Peter Larsson