CAML Query Comparing DateTime with Eq

ferr picture ferr · Mar 14, 2012 · Viewed 16.7k times · Source

I'm trying to put together a CAML Query that compares two DateTime objects, but I cannot get it to work using an Eq comparison. From my testing I can get Gt, Lt, Geq, Leq to work with DateTime comparisons, but Eq doesn't seem to work at all, ever.

The first object is a Date and Time field (produced by InfoPath and saved to a Date and Time field in a SharePoint list), the current example has "3/14/2012 12:00 AM". I have attempted using the [Today /] value, using a hard-coded value in ISO format 2012-03-14T00:00:00Z but nothing has worked so far. I have experimented with IncludeTimeValue, setting it to true/false, no improvement.

My current query looks a little like this,

<Query>
 <Where>
  <Eq>
   <FieldRef Name="SomeDateTimeField" IncludeTimeValue="TRUE" />
   <Value Type="DateTime" IncludeTimeValue="TRUE">2012-03-14T00:00:00Z</Value>
  </Eq>
 </Where>
</Query>

This returns nothing, even though I have an item with that date time in the list. Any ideas?

Answer

Alexander Ulmaskulov picture Alexander Ulmaskulov · Jan 5, 2015

Equal statement can't return anything, because seconds are counted. Try using date range. Sample:

<Where>
 <And>
  <Gt>
   <FieldRef Name='Created' />
   <Value IncludeTimeValue='TRUE' Type='DateTime'>2014-12-10T00:00:00Z</Value>
  </Gt>
  <Lt>
   <FieldRef Name='Created' />
   <Value IncludeTimeValue='TRUE' Type='DateTime'>2014-12-10T23:59:59Z</Value>
  </Lt>
 </And>
</Where>

Please, pay attention, we use same date, but different time.