Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links




Post Categories

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

CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
    @theDate DATETIME,
    @theWeekday TINYINT,
    @theNth SMALLINT
    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

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


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


SELECT theDate


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

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

@RetailWeek varchar(6), @reportType AS char(1)

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

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
ENDCould any one help me out Pleaseeeeeeeeee....Thank you

11/14/2011 12:00 PM | siva
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET