Peter Larsson Blog

Patron Saint of Lost Yaks

How to get the Nth weekday of a month

by Peter Larsson in  , , , ,

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

Legacy Comments
Uri Dimant
2009-06-18		 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
Peso
2009-06-18		 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.
Peso
2009-06-18		 re: How to get the Nth weekday of a month
My function also takes care of computed date is out of scope (month).
Alex Kuznetsov
2009-06-22		 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
Charles
2009-09-30		 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.
Peso
2009-10-01		 re: How to get the Nth weekday of a month
Replace

SELECT theDate

with

SELECT theDate + DATEADD(DAY, DATEDIFF(DAY, @theDate, 0), @theDate)
cialis online
2009-11-17		 re: How to get the Nth weekday of a month
Peso, thanks for snippet :)
Marlene
2010-08-26		 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
siva
2011-11-14		 Urgent Would like to compare with previous years or Months or weeks
Hi Friends,

I was getting the result for selected week... at the same time I would like to show the field for last year same week like this



this is the code

ALTER PROCEDURE [dbo].[KPI_WORK_IN_PROGESS]
@RetailWeek varchar(6), @reportType AS char(1)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @MinRetailWeek AS Varchar(6), @qtr AS varchar(7), @monthStartDate AS datetime

SELECT @qtr=qtr FROM tRetailWeeks WHERE AgrWeek=@RetailWeek

SELECT @monthStartDate=Month_Start_Date FROM tRetailWeeks WHERE AgrWeek=@RetailWeek

SELECT @MinRetailWeek = CASE WHEN @reportType='Y' THEN LEFT(@RetailWeek,4)+'01'
WHEN @reportType='Q' THEN (SELECT MIN(AgrWeek) FROM tRetailWeeks WHERE qtr=@qtr GROUP BY qtr)
WHEN @reportType='M' THEN (SELECT AgrWeek FROM tRetailWeeks WHERE Date=@monthStartDate)
WHEN @reportType='W' THEN @RetailWeek
END

SELECT Stores.Trading_Region_ID

, CASE WHEN Stores.Trading_Region_ID='0010' AND LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'UK Concessions'
WHEN Stores.Trading_Region_ID='0010' AND (LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' OR LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40') THEN 'UK Other'
WHEN Stores.Trading_Region_ID='0010' THEN 'UK Main Stores'
WHEN Stores.Trading_Region_ID='0020' THEN 'USA'
WHEN Stores.Trading_Region_ID='0030' THEN 'Europe'
WHEN Stores.Trading_Region_ID='0080' THEN 'Canada' END AS Trading_Region
, RIGHT(Stores.Location_Type_ID,4) AS Location_Type_ID

, CASE WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'Concession'
WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' THEN 'Outlet'
WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40' THEN 'Other'
WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='50' THEN 'Airport'
WHEN Stores.Trading_Region_ID='0020' THEN 'USA'
WHEN RIGHT(Stores.Location_Type_ID,2)='10' THEN 'Financial District'
WHEN RIGHT(Stores.Location_Type_ID,2)='20' THEN 'Central'
WHEN RIGHT(Stores.Location_Type_ID,2)='30' THEN 'Region' END AS Location_Type
, Trading.Store_ID
, Stores.Store_Name
, SUM(Net_Amount+Tax_Amount) AS Amount
, SUM(Transactions) AS Transactions
, SUM(Quantity) AS Quantity

FROM Sales.Stores_Weekly_Trading AS Trading
INNER JOIN Stores AS Stores ON Trading.Store_ID=RIGHT('000' + RTRIM(Stores.Store_ID),5)
WHERE Retail_Week BETWEEN @MinRetailWeek AND @RetailWeek
GROUP BY Stores.Trading_Region_ID, Trading.Store_ID, Stores.Location_Type_ID, Stores.Store_Name
ORDER By Store_ID
ENDCould any one help me out Pleaseeeeeeeeee....Thank you