# Thinking outside the box

Patron Saint of Lost Yaks

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

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

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

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

, 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'
, 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 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
, Stores.Store_Name
, SUM(Net_Amount+Tax_Amount) AS Amount
, SUM(Transactions) AS Transactions
, SUM(Quantity) AS Quantity