Tuesday, July 31, 2012

CAML Query on datetime columns

I always forget (and end up looking it up again), so I might as well write a blog post so I can search my own blog instead of the entire internet.

The issue is when you are doing a CAML query to get items from a sharepoint list, and want to specify a filter based on a datetime column in that list - to be greater than the current date for example.

There are two issues when querying datetime fields - the first, is comparing datet time values in the correct format, and the second is relevant if you want the query to include the time, and not just the date.
To handle the first, the query requires you to convert the date value you are comparing to, to a format recognised by sharepoint's caml. To do so, you should use
the SPUtility.CreateISO8601DateTimeFromSystemDateTime function on the date value you want to compare to.
Here is an example of testing that the "Expires" column is greater or equal to the current date:

<geq>
             <fieldref name="Expires">
             <value type="DateTime">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now) + @"</value>
          </fieldref></geq>

The second issue is when we want to make the same comparison, but include the time in the query. we have the "Expires" field set to accept both date and a time, the query above will only compare the date - so even items that should have expired an hour ago, would still show - until tomorrow.
To fix that, we need to add IncludeTimeValue='TRUE' to the value node:
<value includetimevalue="TRUE" type="DateTime">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now) + @"</value>

No comments: