Thinking outside the box

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

My Links

Advertisement

News

Archives

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

Gravatar

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

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

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

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

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

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

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

Peso, thanks for snippet :)
11/17/2009 11:39 AM | cialis online
Gravatar

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

# 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

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

Powered by:
Powered By Subtext Powered By ASP.NET