SPQuery with datetime field

Question

hi,

      i need to show created, created by and document name into gridview based on created date.

for ex: I have two textbox one for "from date" and other for "to date" and a button. when user click on the button what are the documents available in document library between the two date. 
i have tried this way,

SPQuery oQuery = new SPQuery();
oQuery.ViewFields = "<FieldRef Name='Created'/>" + "<FieldRef Name='FileLeafRef'/>" + "<FieldRef Name='Author'/>";
DateTime strFromDate = Convert.ToDateTime(txtFromDate.Text);
DateTime strToDate = Convert.ToDateTime(txtToDate.Text);
oQuery.Query = "<Where><FieldRef Name='Created'><Value Type=\"DateTime\" StorageTZ='TRUE'>" + strFromDate + "</Value><FieldRef Name='Created'><Value Type=\"DateTime\" StorageTZ='TRUE'>" + strToDate + "</Value></Where>";
SPListItemCollection items = splist.GetItems(oQuery);
DataTable dt = items.GetDataTable();
return dt;
i am new to this technology.

i don't know how to use the spquery for this scenario.

please anyone can help me.......
thanks in advace........

 

Answer 1

 you need to use AND condition and the "less than" and "greater than" operators to get the result:

your query should look something like this :

string createdBeforeQuery = "<Leq><FieldRef Name=\'Created\'/><Value Type=\'DateTime\'>" + strToDate.ToString("s") + "</Value></Leq>";

string createdAfterQuery = "<Geq><FieldRef Name=\'Created\'/><Value Type=\'DateTime\'>" + strFromDate.ToString("s") + "</Value></Geq>";

oQuery.Query = "<Where><And>" + createdBeforeQuery + createdAfterQuery + "</And></Where>"

at the start comment the oQuery.ViewFields line to be sure that there are no syntax mistakes in it.

check if this helps..

Answer 2

Hi,

In order to make sure that your CAML Query works as it should, you can (and in my opinion, you should) test it with the U2U CAML Query Builder: http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx

What this tool does, is generate queries for your automatically and of course lets you try your existing queries.

If the query is successful in the Query Builder, it should be successfull in your code aswell.
And as previous posted mentioned, try commenting out the viewfields line to make sure there's no errors in there.

Regards,
Tobias Zimmergren
http://www.zimmergren.net

Answer 3

DateTime values have to be converted before you can use them in a CAML query. You can use the SPUtility.CreateISO8601DateTimeFromSystemDateTime() method for this.

Also check this blog entry if you notice the time part of your DateTime values are ignored.

Answer 4

hi All,

   Thanks for your response.

after executing the following code, i am getting "A field or property with the name 'Created' was not found on the selected data source" error.

SPSite spsite = new SPSite(http://localhost);

SPWeb spweb = spsite.OpenWeb();

SPList splist = spweb.Lists["Shared Documents"];

SPQuery oQuery = new SPQuery();

DateTime strFromDate = Convert.ToDateTime(txtFromDate.Text);

DateTime strToDate = Convert.ToDateTime(txtToDate.Text);

string convertedTime = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(strFromDate);

string convertedTime1 = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(strToDate);

string Query = @" <Where><And>

<Leq>

<FieldRef Name='Created' />

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

</Leq>

<Geq>

<FieldRef Name='Created' />

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

</Geq>

</And>

</Where>";

oQuery.Query = Query;

SPListItemCollection items = splist.GetItems(oQuery);

DataTable dt = items.GetDataTable();

return dt;

First Time it was executing fine.
after that the Created column will be vanished.
please help me...

 

Answer 5

I don't see you define any ViewFields in your query. The returned SPListItems only contains fields if you explicitly ask for them.

Like this:

public static string BuildViewFieldsXml(params string[] fieldNames)

{

const string TEMPLATE = @"<FieldRef Name='{0:S}'/>";

StringBuilder sb = new StringBuilder();

foreach (string fieldName in fieldNames)

{

sb.AppendFormat(TEMPLATE, fieldName));

}

return sb.ToString();

}

// Example usage:

SPQuery query = new SPQuery();

query.ViewFields = BuildViewFieldsXml("Title", "Created", "SomeCustomField");

query.Query = ...;  // You know the drill