CAML Queries with DateTime values

I came across an interesting problem whilst using the SPQuery object, CAML and DateTime Fields (at the time I was using them in a Timer Job, but it is not specific to that)

Firstly I found that if you have any problems with the CAML query,

1. the first you know about it is when you try to use the SPList object or SPLIstItemCollection objects on which you based the query.
2. The only error message you are likely to get is "Cannot complete this action, please retry"

One bit that they fail to tell you is that if you are using a DateTime in a CAML query and you are comparing lets say today's date against a Date field in your list, you need to include the property "IncludeTimeValue" and set it to TRUE on the "<Value>" element, so the XML looks like this

  1: <FieldRef Name="MyDate" />
  2: <Value Type="DateTime" IncludeTimeValue="TRUE"> 
  3: </Today> 
  4: </Value>

If you don’t use it then you will get the "Cannot complete this action" error when using the SPListItemCollection object.

Additionally, you need to bear in mind the following when using CAML from your C# code.

1. You need the internal names of your fields when using them in the CAML Code.



2. When using a both a string/choice/lookup field and  a DateTime field in your query using the AND clause, you need 2 AND clauses in there.