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.

Fun with Dates

How many times have you been told that a user group meeting (or any regularly scheduled meeting, for that matter) would take place on the Nth weekday of each month - for example, perhaps the meeting was always on the second Thursday of the month.

Or perhaps you need to send invoices out on the Nth day of each month. For example, I have one client that bills some customers on a NET 10th basis, meaning the 10th of the month following the invoice date is when the bill is due.

So how do you do these things in T-SQL - that is the purpose of this entry. I will handle the latter example first. I created a UDF that will return a DateTime value that is the Nth day of the month following a specified DateTime.

CREATE FUNCTION fnNthOfNextMonth (@OrigDate DateTime, @NthDay TinyInt)
    IF (@Nthday > Day(DateAdd(d, -Day(DateAdd(m, 2, @OrigDate)), DateAdd(m, 2, @OrigDate))))
    RETURN DateAdd(d,  -Day(DateAdd(m, 1, @OrigDate)) + @NthDay,   DateAdd(m, 1, @OrigDate))

The beauty of this function is that it even validates that the Nth day is not more than the actual number of days in the target month. It first adds two months to the original date. Next it determines the Day (day of the month) and makes it negative: -Day(DateAdd(m, 2, @OrigDate)). It then gets the original date + 2 months and adds this negative value to it to get the last day of the month following the original date. Finally, it takes the day of that date and compares it with the Nth value to makes sure Nth is not a day greater than the month actually has.

Examining this with actual values, let's say we have a date of December 30, 2003. Add 2 months to this and you get February 28, 2004 (yes, really, this is what you would get). The Day of that date is 28, so add -28 days to February 28 and you get... drum roll, please... January 31, 2004, which gives us a Day Value of 31. And if the Nth value is greater than the number of days in the target month, the UDF simply returns NULL.

The RETURN statement uses the same methodology, except it uses the original date + 1 month, and then adds the Nth day back, to get the Nth day of the following month.

So, using the example of December 30, 2003 again: Add a month, resulting in January 30, 2004. This date would have a -Day value of -30 to which we add our Nth day, let's say 10. So now the value is -20, which, if subtracted from January 30, 2004, would result in January 10, 2004. Nice.

This entry is getting too long, so I will sign off for now and continue another day and address our first issue, the Nth weekday of a month.

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