## 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 | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## Feedback

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

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

## # 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 currentSET DATEFIRST

setting. Also my function can calculate the last friday, for example, as well as the first or second tuesday.

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

My function also takes care of computed date is out of scope (month).## # 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

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

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

ReplaceSELECT theDate

with

SELECT theDate + DATEADD(DAY, DATEDIFF(DAY, @theDate, 0), @theDate)

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

Peso, thanks for snippet :)## # re: How to get the Nth weekday of a month

HiCan 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

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