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.

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)
RETURNS DateTime
AS
BEGIN
    IF (@Nthday > Day(DateAdd(d, -Day(DateAdd(m, 2, @OrigDate)), DateAdd(m, 2, @OrigDate))))
        RETURN NULL
    RETURN DateAdd(d,  -Day(DateAdd(m, 1, @OrigDate)) + @NthDay,   DateAdd(m, 1, @OrigDate))
END

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

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