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.