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

Comments

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