THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

CREATE FUNCTION fnNthWeekdayOfMonth
 (@DateInMonth DateTime, @NthWeek TinyInt, @Weekday TinyInt)
RETURNS DateTime
AS
BEGIN
 -- 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 )))
  RETURN NULL

 -- 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))))
  RETURN NULL

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

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
7

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement