UPDATED: Rebuilt project with the 2011 Q2 version of RadScheduler

You might have noticed that RadScheduler uses very cryptic strings to describe repeating appointments. We have decided not to reinvent the wheel and in fact this format is closely based on the iCalendar (RFC2445) standard. Here is an example:



This rule says: "The event starts at midnight on 18th of February 2008, lasts one day and repeats each year, for 3 years, on the second to last Monday of February". Now say this three times fast.

As much as standards-based those strings are, they are useless if you want to process your appointments on the database server itself. There are many reasons for you to want to do this. You might want to check for conflicting appointments. Or you might need to prepare some statistics for a report.

From version 2005 and up, SQL Server supports Managed Stored Procedures and User-Defined Functions (UDF). This exciting feature lets you plug in your own managed code into SQL Server and use it inside your queries. This is exactly what we need.

The solution we are presenting comes in the form of a managed UDF. This function uses the same recurrence engine as RadScheduler to do the heavy lifting required to evaluate the rules.

The attached file contains a setup script that enables CLR integration, registers the two assemblies (recurrence engine, UDF) and creates the function itself. Full source code of the UDF is included. Be sure to read the section on CLR integration on MSDN. There are good reasons that it is not enabled by default.

Let's see how all this comes together. A common scenario is to request all appointments in a given time range. Here is how we can do this with our function:

SET @@RangeStart = CAST('2007-03-31' AS DATETIME)

SET @@RangeEnd = CAST('2007-04-05' AS DATETIME)

-- Will list all appointments (recurring or not) in the given range
ISNULL(o.StartDate, a.Start) as Start,
ISNULL(o.EndDate, a.[End]) as [End]
FROM Appointments a
OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
WHERE -- Include non-recurring appointments in the range
(a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR
-- And recurring appointments in range.
(a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart)


This yields the following results:

ID Subject Start End
24 Meeting with Alex 2007-03-31 06:00:00 2007-03-31 07:00:00
30 Test Appointment 2007-03-31 07:30:00 2007-03-31 08:30:00
15 Technical meeting 2007-04-02 06:30:00 2007-04-02 07:30:00
15 Technical meeting 2007-04-03 06:30:00 2007-04-03 07:30:00
15 Technical meeting 2007-04-04 06:30:00 2007-04-04 07:30:00


You can see how "Technical meeting" has been expanded into several appointments, according to it's recurrence rule.

Additional resources:

Have fun!

* Applicable to SQL Server 2005 and up

 Attached:  ExpandRecurrence_UDF_20010903

About the Author

Iana Tsolova

is Product Manager at Telerik’s DevTools division. She joined the company back in the beginning of 2008 as a Support Officer and has since occupied various positions at Telerik, including Senior Support Officer, Team Lead at one of the ASP.NET AJAX teams and Technical Support Director. Iana’s main interests are web development, reading articles related to geography, wild nature and latest renewable energy technologies.

Related Posts


Comments are disabled in preview mode.