CLR integration – SQL

Just written my first C# SQL function, here it is in all its glory.

[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime GetSunday(DateTime original)
{
    DateTime result
= new DateTime(original.Year, original.Month,
        original.Day
((int) original.DayOfWeek),
       
0, 0, 0);
   
return result;
}

Then I went and found out how to really go to town. http://msdn.microsoft.com/…/sqlclrguidance.asp, so here is my 2nd function (and helper)

 

[Microsoft.SqlServer.Server.SqlFunction
    (FillRowMethodName
=FillGetAllDates,
    TableDefinition
=Date datetime)]
public static IEnumerable GetAllDates(DateTime start, DateTime end)
{
    DateTime date = start.Date;
    List
<DateTime> result = new List<DateTime>();
   
while (date <= end)
    {
        result.Add(date);
        date
= date.AddDays(1);
    };
   
return result;
}

public static void FillGetAllDates(object row, out DateTime date)
{
    date
= (DateTime) row;
}

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s