How to get the Nth weekday of a month

<< 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

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

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

Hi
Can you help me?? I want to determine the second working day of the month. Also considering public holidays. So say the first working day of the month is a public holiday, the actual first working day would be the next day and the second working day, 2 days later. i.e. First working day is a Monday, but it is a public holiday too.. so the first working day is the Tuesday and the second working day is the Wednesday. Is there an easy automated way to determine this?
Many Thanks
Marlene

8/26/2010 3:22 PM | Marlene

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 1 and 5 and type the answer here: Enter the code shown above:

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories