locked
camel query issue between date time range RRS feed

  • Question

  • Calendar has one event item called Weekly Meeting

    Using the following camel query. The input parameters are fromDate  and  toDate. The above result is not showing.      

    fromDate - 10/17/2017 09:00 am  
    toDate -  10/17/2017 11:30 am  

       query.Query = "<Where><And><Geq><FieldRef Name='EventDate' /><Value IncludeTimeValue='true' Type='DateTime'>" + fromDate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Geq><Leq><FieldRef Name='EndDate'/><Value IncludeTimeValue='true' Type='DateTime'>" + toDate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Leq></And></Where>



    Tuesday, October 24, 2017 1:58 PM

Answers

  • Hi,

    Here is a demo for your reference and I have tested successfully.

    public static SPListItemCollection GetNextEvents(SPList eventCalendar)
            {
                DateTime startDateTime = DateTime.Parse("10/24/2017 09:00 am");
                DateTime endDateTime = DateTime.Parse("10/24/2017 11:30 am");
                string startDateTimeString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(startDateTime);
                string endDateTimeString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(endDateTime);
                SPQuery query = new SPQuery();
                query.RowLimit = 3;
                query.ExpandRecurrence = true;            
                query.Query = "<Where>" +                    
                                  "<And>" +
                                    "<Geq>" +
                                        "<FieldRef Name='EndDate' />" +
                                        "<Value Type='DateTime' IncludeTimeValue='True'>" +
                                            startDateTimeString +
                                        "</Value>" +
                                    "</Geq>" +
                                    "<Leq>" +
                                        "<FieldRef Name='EventDate' />" +
                                        "<Value Type='DateTime' IncludeTimeValue='True'>" +
                                            endDateTimeString +
                                        "</Value>" +
                                    "</Leq>" +
                                  "</And>" +
                                "</Where>" +
                                "<OrderBy>" +
                                    "<FieldRef Name='EventDate' />" +
                                "</OrderBy>";            
    
                SPListItemCollection itemColl = eventCalendar.GetItems(query);
    
                return itemColl;
            }
    

    Screenshot of my event item:

    Best regards,

    Lee Liu


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by ghsajith Thursday, October 26, 2017 6:14 AM
    Wednesday, October 25, 2017 6:33 AM

All replies

  • Hi ,

    Seems like your CAML query is correct .  I assume that  fromDate.ToString("yyyy-MM-ddTHH:mm:ssZ")   format might causing the issue .

    Please try U2U CAML Query Builder to get the queries right ..

    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    Tuesday, October 24, 2017 2:58 PM
  • Tried in U2U CAML Query Builder. No difference
    Tuesday, October 24, 2017 6:45 PM
  • try below

    fromDate - 17/10/2017 09:00 am  
    toDate -  17/10/2017 11:30 am  

    Instead of

    fromDate - 10/17/2017 09:00 am  
    toDate -  10/17/2017 11:30 am  

    Tuesday, October 24, 2017 8:24 PM
  • Hi

    try this

    string fromDateStr = SPUtiltity.CreateISO8601DateTimeFromSystemDateTime(fromDate);
    string toDateStr = SPUtiltity.CreateISO8601DateTimeFromSystemDateTime(toDate);
    query.Query = "<Where>"<And><Geq><FieldRef Name='EventDate' /><Value IncludeTimeValue='true' Type='DateTime'>" + fromDateStr+ "</Value></Geq><Leq><FieldRef Name='EndDate'/><Value IncludeTimeValue='true' Type='DateTime'>" + toDateStr + "</Value></Leq></And></Where>";

    Wednesday, October 25, 2017 1:32 AM
  • Tried both above options. Still not  working

    When I change the  toDate to  10/17/2017 12:00 it is working. Value less than 12 is not working  

    • Edited by ghsajith Wednesday, October 25, 2017 5:18 AM update
    Wednesday, October 25, 2017 5:17 AM
  • Hi,

    Here is a demo for your reference and I have tested successfully.

    public static SPListItemCollection GetNextEvents(SPList eventCalendar)
            {
                DateTime startDateTime = DateTime.Parse("10/24/2017 09:00 am");
                DateTime endDateTime = DateTime.Parse("10/24/2017 11:30 am");
                string startDateTimeString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(startDateTime);
                string endDateTimeString = SPUtility.CreateISO8601DateTimeFromSystemDateTime(endDateTime);
                SPQuery query = new SPQuery();
                query.RowLimit = 3;
                query.ExpandRecurrence = true;            
                query.Query = "<Where>" +                    
                                  "<And>" +
                                    "<Geq>" +
                                        "<FieldRef Name='EndDate' />" +
                                        "<Value Type='DateTime' IncludeTimeValue='True'>" +
                                            startDateTimeString +
                                        "</Value>" +
                                    "</Geq>" +
                                    "<Leq>" +
                                        "<FieldRef Name='EventDate' />" +
                                        "<Value Type='DateTime' IncludeTimeValue='True'>" +
                                            endDateTimeString +
                                        "</Value>" +
                                    "</Leq>" +
                                  "</And>" +
                                "</Where>" +
                                "<OrderBy>" +
                                    "<FieldRef Name='EventDate' />" +
                                "</OrderBy>";            
    
                SPListItemCollection itemColl = eventCalendar.GetItems(query);
    
                return itemColl;
            }
    

    Screenshot of my event item:

    Best regards,

    Lee Liu


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by ghsajith Thursday, October 26, 2017 6:14 AM
    Wednesday, October 25, 2017 6:33 AM
  • Thanks a lot.It is working fine now.
    Thursday, October 26, 2017 6:14 AM