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

• Anonymous
  January 07, 2013
  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

• Anonymous
  January 07, 2013
  my code which is not working protected void btnRegister_Click(object sender, ImageClickEventArgs e)    {        string strURL = "http://inedec-it-09:321/FA/Admin/";        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;            }        }    }