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> +

            <Eq>

               <FieldRef Name='Title' />

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

            </Eq>

            <And>

              <Eq>

                  <FieldRef Name='from_x0020_date' />

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

              </Eq>

         </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";

                   }

                   else

                   {

                           SPListItem oListItem0 = oList.Items.Add();

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

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

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

               oListItem0.Update();

                           lblShowMsg1.Text = "Updated";

                   }

                   oWebsiteRoot.AllowUnsafeUpdates = false;

               }

           }

       }

Skip to main content