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;
}
Subscribe