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.

Still More Fun with Dates in SQL Server "Yukon"

In keeping with my previous post, I wanted to show you how you could create the NthOfNextMonth function in .NET and then use it in SQL Server. The function, shown here, look amazingly similar to the T-SQL function.

using System;

using System.Data.Sql;

using System.Data.SqlTypes;

 

namespace Wintellect.SQLServer

{

  public class CDateFunctions

  {

    [SqlFunction]

    public static SqlDate NthOfNextMonth(SqlDate OrigDate, SqlByte NthDay)

    {

      if (OrigDate.IsNull == true || (Boolean)(NthDay > OrigDate.AddMonths(2).AddDays(-(OrigDate.AddMonths(2).Day)).Day))

        return SqlDate.Null;

      return OrigDate.AddMonths(1).AddDays(-(OrigDate.AddMonths(1).Day) + NthDay);

    }

  }

}

Actually, this is the entire class, to which we could add other static methods that would be usable as UDFs in SQL Server. Notice how this C# method uses the type SqlDate instead of the DateTime type you would normally expect. Since the function will be receiving its data from SQL Server, the type is one of the new SqlTypes. I could have converted it to DateTime to do the work, but why bother when SqlDate has everything I need to do the job just fine.

Now, to use this in SQL Server “Yukon“, you will have to register the assembly and then create the function,  referencing the static method in the assembly when doing so.

CREATE ASSEMBLY DateFunctions FROM 'C:\Projects\Yukon\DateFunctions\bin\release\DateFunctions.dll'
GO

CREATE FUNCTION fnxNthOfNextMonth (@OrigDate Date, @NthDay TinyInt)
RETURNS Date
AS
EXTERNAL NAME DateFunctions:[Wintellect.SQLServer.CDateFunctions]::NthOfNextMonth
GO

You are now ready to use the UDF. The best part is, it's just a UDF, so you use it like the others I created in the previous posts.

SELECT dbo.fnNthOfNextMonth( CAST('1-24-2004' As DateTime), 5),
       dbo.fnNthOfNextMonth2( CAST('1-24-2004' As Date), 5).ToString(),
       dbo.fnxNthOfNextMonth( CAST('1-24-2004' As Date), 5).ToString()

All three of these functions give the same result, but each is very different from the others in how it was implemented.

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. [Hey, are you even reading this?] 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