I often need to calculate a day number in a month. For example, I need to work out the 3rd Tuesday of a month, or the 4th Friday, and so on. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It's a function called DayNumberOfMonth.
It's a function that takes four parameters:
@Year is the year (of the month in question)
@Month is month in question (as a number within a calendar year i.e. March = 3)
@DayOfWeek is an integer with Sunday = 1, Monday = 2, etc.
@DayNumber is also an integer for the day number (i.e. 3 for 3rd Monday)
You can use our tools as a set or as a great example of how to write functions like these.
Find out more
You can see it in action in the main image above, and in the video here:
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
http://sdutools.sqldownunder.com
See https://www.sqltopia.com/algorithms/date-and-time/get-the-nth-weekday-of-any-interval/
Doc… I very much appreciate your generosity to the community at large but you really need to stop using WHILE loops for these things.
Generally when I do, there's a reason. For this one, do you have an example of what you'd prefer, that would work more efficiently (and keep in mind, on 2008+) ?
Heh… fair enough. Generally, if it looks like there's a reason to use a While Loop, I look for a faster method, and there's usually one to be had. And apologies for the late reply. I lost track of this thread.
You should look into Itzik Ben-Gan's method of creating an inline Tally Table using his cCTE method (Cascading CTE is how I refer to them). It would allow you to convert your function into a much faster iTVF (Inline Table Value Function) or just a (usually) faster Scalar function .
There's no need even for that in this case, though. Instead of generating all of the days of the month like you're doing, a bit of temporal math will do us fine. The only reason why I used CASE is so that it will be 2008 compatible. You can convert it to a Scalar Function but it can also be converted to an iTVF. In 2019, the new stuff there will have a much better chance of inlining the scalar function (again, usually) if it doesn't have to content with a loop.
I hope this site preserves leading spaces so the formatting i put into this is preserved but I doubt it. Here's the code as a 100% compatible replacement. It runs at about 35.3 time faster as a scalar function. If it's converted to an iTVF and you modify the code that uses it to accept it as an iTVF, it runs 116.4 times faster.
Here's the code.
–Note, change to SDU_Tools.DayNumberOfMonth if you decide to use it.
CREATE FUNCTION dbo.DayNumberOfMonth_JBMTest
/**********************************************************************************************************************
Function: Returns the Nth nominated day of the month
Parameters: @Year INT – the 4 digit year
@Month INT – the 1 or 2 digit month
@DayOfWeek INT – Sunday = 1, Monday = 2, etc.
@DayNumber INT – day number (i.e. 3 for 3rd Monday)
Action: Returns the Nth nominated day of the month
Return: DATE
Refer to this video: https://youtu.be/BeVXs-J4soo
———————————————————————————————————————–
Test Examples:
SELECT SDU_Tools.DayNumberOfMonth(2020, 2, 1, 1); — first Sunday of Feb 2020
SELECT SDU_Tools.DayNumberOfMonth(2020, 2, 1, 2); — second Sunday of Feb 2020
SELECT SDU_Tools.DayNumberOfMonth(2020, 2, 3, 2); — second Tuesday of Feb 2020
SELECT SDU_Tools.DayNumberOfMonth(2020, 2, 4, 3); — third Wednesday of Feb 2020
———————————————————————————————————————–
Revision History:
Rev 00 – 04 Mar 2020 – Dr. Greg Low
– Initial concept published.
Rev 01 – 26 Jun 2021 – Jeff Moden
– 100% Usage compatible performance enhancment (35.3X)
**********************************************************************************************************************/
(
@Year INT
,@Month INT
,@DayOfWeek INT
,@DayNumber INT
)
RETURNS DATE AS
BEGIN
RETURN
(
SELECT f3.NDate
FROM (VALUES (DATEADD(mm,(@Year-1900)*12+@Month-1,0))) f1(FoM)
CROSS APPLY (VALUES (DATEADD(dd,DATEDIFF(dd,7,f1.FoM)/7*7,6)+@DayOfWeek-1)) f2(FDoW)
CROSS APPLY (VALUES (f2.FDoW + CASE WHEN f2.FDoW < f1.FoM THEN @DayNumber*7 ELSE (@DayNumber-1)*7 END))f3(NDate)
WHERE MONTH(f3.NDate) = @Month
)
END
;
Gosh…. I hope my post just went through. I can tell it it went for "monitoring" or if it just failed to save.
It looks like my coded response prior the previous one got lost. Is there something special I need to do to post code on this site?
Nothing special Jeff. It just landed in the spam queue rather than the moderation queue. I'll look to add the change in. There are many of these that I will update over time, as time permits.
Just a note… the way the code I post works is…
1. Find the date of the first of the month (FoM).
2. Find the date of the beginning of the week (a Sunday, which is treated as "Day 0") that contains the FoM.
3. Add the desired DayOfWeek (minus 1 because it's all zero based rather than unit based) to the results of step two to find the day of the week in that same first week (FDoW).
4. If that FDoW is less than the FoM, then add the desired DayNumber * 7 days (Day number of weeks) the the FDoW and we're done. If the FDow is greater than or equal to the FoM, the add one week less days ((DayNumber-1)*7) than the FDoW and we're done.
Lordy. The forum software sure made a mess of that last list. It was a nice numbered list and now it's just a blob of text.
Hey, it's just wordpress comments 🙂