THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

More Fun with Dates

I couldn’t wait, and so I am continuing this topic a might sooner than expected (the original entry can be found here). In that first entry, I had spoken about using T-SQL to find the Nth weekday of a month given a date in that month. Without further ado (pun intended), here is the UDF to do just that:

 (@DateInMonth DateTime, @NthWeek TinyInt, @Weekday TinyInt)
 -- if weekday is invalid or if week is invalid for specified month, return null
 IF (NOT (@Weekday Between 1 And 7)
    AND (@NthWeek > Ceiling(Day(DateAdd(d, -Day(DateAdd(m, 1, @DateInMonth)), DateAdd(m, 1, @DateInMonth))) / 7.0 )))

 -- Find first day of month for specified date
 SET @DateInMonth = DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth)

 -- Determine how many days to add to 1st of month to get the target date
 DECLARE @DaysToAdd TinyInt

 SET @DaysToAdd = 7*(@NthWeek - 1) +
   (@Weekday - DatePart(weekday, @DateInMonth)) +
   Sign(Sign(@Weekday - DatePart(weekday, @DateInMonth)+ 1) - 1) * -7

 -- If target date is past end of month, return null
 IF (@DaysToAdd >= Day(DateAdd(d, -Day(DateAdd(m, 1, @DateInMonth)), DateAdd(m, 1, @DateInMonth))))

 -- All is well, return the date
 RETURN DateAdd(d, @DaysToAdd , @DateInMonth)

The concept is really straightforward; it’s the implementation that is more complex. Calculating the number of days to add to the first of the month is the really the only tough item here. I will explain how it works using real data: Looking at the calendar for January 2004, you will see that the 1st of the month is on a Thursday. Say you want to find the 2nd Tuesday (weekday value = 3) of the month (that would be the 13th): @DaysToAdd will need to be 12 in order to get the correct date (1st of the month + 12 = 13th). The first step is to get a number equal to the number of days in the 1 less than the desired number of weeks. The code subtracts 1 from @NthWeek value, multiplies the result by 7, and adds one. Using the example, that would be 7 * (2 – 1), which would equal 7. Remember that number.

Next, the weekday value of the first of the month is subtracted from the desired weekday. Using the example, this would be 3 - 5, which would result in -2. Add that to the value from before (did you forget about the 7 already?) to give 5 (now forget about the 7 and remember the 5 instead).

This value represents the number of days that will be either: The number we want or 7 less than the number we want; the results depend on if the desired weekday is before (7 less), the same as (7 less) or after (the exact amount) the weekday of the 1st of the month.
Finally, the last calculation is similar to the previous one and can be seen here using the example data:

Sign(Sign(3 – 5 + 1) - 1) * -7 results in
Sign(Sign(-1) - 1) * -7 results in
Sign(-1 - 1) * -7 
results in
Sign (-2) * -7
 results in
-1 * -7 
results in

Take this 7 (which is really the number of days from the 1st of the month until the ) and add it to the 5 and you get… tada… 12. This last number is 7 when the desired weekday is on or before the weekday of the 1st of the month and 0 when it is after.

If you’re thinking that I could have used an if statement and kept the complexity of the assignment of @DaysToAdd to a minimum, and you’re are correct. The following could have replaced the more complicated assignment:

IF Sign(@Weekday - DatePart(weekday, @DateInMonth)) = 1
 SET @DaysToAdd = 7 * @NthWeek - 1) + (@Weekday - DatePart(weekday, @DateInMonth))
 SET @DaysToAdd = 7 * @NthWeek + (@Weekday - DatePart(weekday, @DateInMonth))

I also could have gone the other extreme and replaced everything from the assignment of @DateInMonth to be the first of the month up through the assignment of @DaysToAdd with a single line of code, shown here:

SET @DaysToAdd = 7 * (@NthWeek - 1) + (@Weekday - DatePart(weekday, DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth))) +  Sign(Sign(@Weekday - DatePart(weekday, DateAdd(d, -Day(@DateInMonth) + 1 , @DateInMonth))+ 1) - 1) * -7

The point here is that there are many possible solutions, of which I am showing only one - well, actually three because you ask so many questions. ;-)

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta


No Comments
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement