CAML filtering using a time

CAML is the query language used to get content from libraries and lists in a SharePoint environment. The keywords are reminiscent of SQL, but they have been formatted to be XML. This means they are very hard to read and write. This is why many use the great (and free) u2u CAML builder.

There are two pittfalls I dove into headfirst recently. When you create a custom control in c# you can specify a query to be executed on a SPList object. Easiest is to create your query in u2u and then copy-paste it into your code. However, you should remove the <query> and </query> tags from your query. If you don't, the result will be all items from the list. Your query will do nothing. This can be quite hard to spot if you expected most or all your items to be returned.

The second pittfall was in my query parameters. I wanted all items where a DateTime field was lower then "now". Now can be specified as
<value type="DateTime"><today /></value>
or you can get your code to output something like this:
<value type="DateTime">2008-08-08T16:34:07Z</value>

This will work. Kinda. It may take some time before you notice that the query processes the date just fine. However, the time part will be ignored. The second example effectively filters like it was:
<value type="DateTime">2008-08-08T00:00:00Z</value>

If you want the time value to be used you've got to say:
<value type="DateTime" includedatetime="'TRUE'"><Today /></value>


Note: I used both the <Today /> and the yyyy-mm-ddThh-mm-ssZ format in my examples. Both exhibit the same behaviour on dates.