SPQuery with Boolean and DateTime

  • Article

This isn't anything really new or unique but seemed worth bloging to have it one in place. A SPQuery can be used to retrieve some SPListItems from a SPListItemCollection.

If you want to query on a Boolean field such as a Yes/No field (boolean) your query CAML might look like the following:

query.Query = "<Where><Eq><FieldRef Name='LastChange' /><Value Type='Boolean'>1</Value></Eq></Where>";

Note the value for the boolean expressed as text. 1 for true, 0 for false.

The following code is an example which shows how to do a DateTime comparison. It is very important to use a correct date format as the value in the inner xml of the where clause.

query.Query = "<Where><Eq><FieldRef Name='LastChange' /><Value Type='DateTime'>1971-01-01T00:00:00Z</Value></Eq></Where>";

or with the current datetime you could use:

query.Query = String.Format("<Where><Gt><FieldRef Name='Modified'/><Value Type='DateTime' StorageTZ='TRUE'>{0}</Value></Gt></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow));