SPQuery with Boolean and DateTime

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));

Comments (2)

  1. swapnil more says:

    i have a list whre from date is selected bu user and looking for a spquery to create list item thru spquery wherein from date and current is not already exist in the list.

    How to write a spquery

  2. swapnil more says:

    my code which is not working

    protected void btnRegister_Click(object sender, ImageClickEventArgs e)


           string strURL = "http://inedec-it-09:321/FA/Admin/&quot;;

           string Nme;

           string dtc;

          // string bs;

           string  duplicate = "False";

           using (SPSite oSiteCollection = new SPSite(strURL))


               using (SPWeb oWebsiteRoot = oSiteCollection.OpenWeb())


                   oWebsiteRoot.AllowUnsafeUpdates = true;

                   SPList oList = oWebsiteRoot.Lists["G2buses"];

                   Nme = System.Environment.UserName.ToString();

                   //bs = DropDownList1.SelectedIndex.ToString();

          dtc = Calendar1.SelectedDate.ToString();

          SPQuery oQuery = new SPQuery();

          oQuery.Query = @"<Where> +


               <FieldRef Name='Title' />

               <Value Type='Text'>"+ Nme + @"</Value>




                  <FieldRef Name='from_x0020_date' />

          <Value Type='DateTime'>"+ dtc + @"</Value>


         </And> </Where>";

                   SPListItemCollection collListItems = oList.GetItems(oQuery);

                   foreach (SPListItem item in collListItems)


                       duplicate = "True";


                   if (duplicate == "True")


                       lblShowMsg1.Text = "You Have Alredy Been Registered!";

                       //lblShowMsg.Text = "32 Seats";




                           SPListItem oListItem0 = oList.Items.Add();

                           oListItem0["Title"] = System.Environment.UserName;

                           oListItem0["Bus"]= DropDownList1.Text.ToString();

                           oListItem0["from date"]= Calendar1.SelectedDate.ToString();


                           lblShowMsg1.Text = "Updated";


                   oWebsiteRoot.AllowUnsafeUpdates = false;




Skip to main content