How to deal with datetime column in SharePoint list

Question

I use SPQuery before. But always used text fields. This time, I am deal with datetime field. It is fine when I run the query in U2U Caml Query Builder which return me 30 items that is correct. However, when I copied the text to the C# code, it return 0 item.

In U2U CAML Query Builder

In C# code

I just wonder if someone can tell me (or even better to modified the code) how to deal with the date time column. The filters are: (1) InvoiceCompleted must be "Open" (2) SubmitDate has 35 day or more old. Thanks lot.

Answer 1

Thanks. This solution does not work for me. It return too many items. I tried the following method. It seem work for me:

        DateTime Day35 = DateTime.UtcNow.AddDays(-35);
        string nedt = Day35.ToString("yyyy-MM-dd");
        SPQuery iQuery = new SPQuery();
        //"<Where><And><Leq><FieldRef Name='SubmitDate'/><Value Type='DateTime'>" + nedt + "</Value></Leq>" +          
        iQuery.Query = "<Where><And><Leq><FieldRef Name='SubmitDate'/><Value Type='DateTime'>" + nedt + "</Value></Leq>" +
                       "<Eq><FieldRef Name='InvoiceCompleted'/><Value Type='Text'>Open</Value></Eq></And></Where>";

Answer 2

Hi @Er Bo Guan ,

To filter the date time column SubmitDate has 35 day or more old, just modify the query for column SubmitDate to the below:

<Leq><FieldRef Name='SubmitDate  ' /><Value Type='DateTime'><Today Offset='-30' /></Value></Leq>  

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.