Visual Studio Tools for Office and Filter Strings with Dates

As you now, most of my time these days is spent working in the mobile space however I do work quite a bit with other areas of development including VSTO – what is officially called Microsoft Visual Studio 2005 Tools for the Microsoft Office System.; in other words, building Office solutions using .NET.

I ran into a rather simple but frustrating issue the other day while building an Outlook Add-in with C#. The problem cropped up while working with a filter string containing a date and passing that filter string to the Restrict function to locate an appointment in the Outlook calendar.

Basically I had the following code to locate an appointment with a specific Subject and Start date/time.

string filter = string.Format("[Subject] = '{0}' AND [Start] = '{1}'",
                "My Subject", showingDateTime);
Outlook.MAPIFolder calendarFolder =
                Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar);
Outlook.Items appointmentItems = calendarFolder.Items.Restrict(filter);

When this code is run the value of “filter” contains the following.

[Subject] = 'My Subject' AND [Start] = '12/02/2005 02:45:00 PM'

The problem was that when this code runs, an exception is thrown indicating a “Type Mismatch in the value 12/02/2005 02:45:00 PM”.

Now that certainly looks like a valid date value to me. So I wondered if maybe I had done something else wrong so I double checked the proper format for a Restrict filter string – that checked out. I checked to make sure that dates are supposed to be specified in single quotes – they are.

So after trying a large number of things that didn’t work I looked at some of the filter string examples I had found more closely and something very important stood out. The examples never showed seconds in the time, only hours, minutes and AM/PM. At first I thought that couldn’t possibly be it – even if the seconds aren’t allowed in a date value, the exception would certainly give a better error message then “Type Mismatch”.

But thought I better give it a try so I changed the line that creates the filter string to the following.

string filter = string.Format("[Subject] = '{0}' AND [Start] = '{1}'",
                "My Subject", showingDateTime.ToString("MM/dd/yyyy HH:mm"));

Which creates this filter string:

[Subject] = 'My Subject' AND [Start] = '12/02/2005 14:45'

Ran the program and viola – works perfectly.

I also tried formatting the filter to contain AM/PM (02:45 PM) instead of the 24-hour format and the AM/PM format works fine as well.

The whole problem had been the presence of seconds in the date - Argh!

I know its a small thing, but certainly not something I expected to encounter. Hopefully as the .NET/Office relationship matures, little type differences like this between .NET and the underlying Office implementations will go away.