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 - SQL Server "Yukon" style

So, to kind of give you all a quick introduction to SQL Server “Yukon”, I though I would revisit a previous post that calculated the Nth day of the month following a specified Date. No, this is not a typo - Date is a new data type that stores only the date and can represent any date from 1 January 0001 to 31 December 9999. Date is special in that it is really CLR-based and thus has methods and properties that can be addressed, even in T-SQL. Using this date type, the fnNthDayOfNextMonth function could be rewritten as follows...

CREATE FUNCTION fnNthOfNextMonth (@OrigDate Date, @NthDay TinyInt)
RETURNS Date
AS
BEGIN
    IF (@NthDay >  @OrigDate.AddMonths(2).AddDays(-(@OrigDate.AddMonths(2).Day)).Day)
        RETURN NULL
 RETURN @OrigDate.AddMonths(1).AddDays(-(@OrigDate.AddMonths(1).Day) + @NthDay)
END

Because I am using Date's methods (AddMonths and AddDays) and properties (Day), this function looks very different, even though it is doing the same task. I have mapped the clause from the IF statement from the previous version to this version to show you what is going on.

@OrigDate.AddMonths(2).AddDays(-(@OrigDate.AddMonths(2).Day)).Day

Day(DateAdd(d, -Day(DateAdd(m, 2, @OrigDate)), DateAdd(m, 2, @OrigDate))))

Add two months to the current date, take the negative number of days from that result. Now add two months to the current date, and then add the previous negative result as days to this to find the last day of the next month. Then take the day value from that to be used to comparison.

I hope the color coding helps make it clearer. This shows the influence of .NET on T-SQL is more than just the ability to write code in C# or VB.NET. And speaking of which, in the next post, I will write this code in C# as a User-Defined Function and show how to use that in T-SQL.

Happy Programming...

Disclaimer: Since SQL Server “Yukon” is still in Beta 1, features may change between now and when it is actually released as Beta 2 and RTM. Any topic related to SQL Server “Yukon” discussed herein is subject to change without notice. And although I will try my darnedest to make you aware of any such changes, I cannot guarantee any such notice.

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