locked
CAML Query with Datetime problem RRS feed

  • Question

  • Hello All

    I am have 1 Sharepoint Date control from where I am storing value and adding hours like this

               DateTime schDate = this.txtScheduleDate.SelectedDate;   

               
                if (ddlInspectionTime.Text.Trim() == "9:00 AM")
                {
                    schDate = schDate.AddHours(9);
                }
                else
                {
                    schDate = schDate.AddHours(13);
                } 

    I have two records with following datetime values

    5/14/2010 9:00:00 AM
    5/17/2010 1:00:00 PM

    When I select startdate and end date as 05/14/2010 it wont return any record, but if I select start date as 05/13/2010 and 05/15/2010, it returns 1 record. Why it is not picking if I select startdate and enddate as 05/14/2010? Samething happening with other dates also. I am using following CAML query. Same thing I checked with CAML query builder it also return same  behaviour.

    oQuery.Query = "<Where><And><Geq><FieldRef Name='Start_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"+SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate)+"</Value></Geq><And><Leq><FieldRef Name='End_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"+SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate)+"</Value></Leq><Eq><FieldRef Name='QA_x0020_Employee' /><Value Type='Lookup'>"+strFullName+"</Value></Eq></And></And></Where>";

    Thursday, May 13, 2010 8:10 PM

Answers

  • Here is the modified query as you suggested and run thru U2U, returning 1 record. When I checkeed in in code it is not returning anything for selected date 5/28/2010 1:00:00 PM

     <Query>
       <Where>
          <And>
             <Leq>
                <FieldRef Name='End_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:34Z</Value>
             </Leq>
             <Geq>
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:46Z</Value>
             </Geq>
          </And>
       </Where>
    </Query>


    You only got rid of the "QA Inspector" from the formula. Other criteria are still the same. The Geq and Leq were wrong in your query. I make change as follow (bold). Copy and paste the query in U2U, see if anything difference.

     <Query>
       <Where>
          <And>
             <Geq >
                <FieldRef Name='End_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T09:00:00Z </Value>
             </Geq >
             <Leq >
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T09:00:00Z </Value>
             </Leq >
          </And>
       </Where>
    </Query>


    -Bin
    Friday, May 14, 2010 5:07 PM
  • Here is my reply in Italic:

    3. You need a report/view to pull records from the list, according to your CAML, that StartDate>=ScheduleDate and EndDate<=ScheduleDate Yes

    If I understand your situation correctly above, there are two questions here:

    1. How come StartDate>=EndDate? At least that is the logic in your query. I thought StartDate should always be earlier than EndDate. No, Please check query again, I may be wrong. In plain text my query is StartDate >= ScheduleDate and EndDate <=ScheduleDate.

    Let me explain how the logic works here:

     EndDate <=ScheduleDate also means ScheduleDate>=EndDate, which add back to the StartDate >= ScheduleDate, then means

    StartDate >= ScheduleDate >= EndDate, get rid of the ScheduleDate you get StartDate >= EndDate

    Note: ">=" can be passed on.     e.g.    5>=4>=3  means 5>=3

    2. What are the StartDate, EndDate in your list, and ScheduleDate value as well? e.g.

     If ScheduleDate is 05/14/2010 09:00 AM

                    StartDate                     EndDate
    record 1 5/14/2010 08:00 AM   5/14/2010 10:00 AM
    Note: That means if we are schedueling any assignment at 05/14/2010 at 09:00 AM it should return 1 record, because user is is out for two hours between this datetime range (5/14/2010 08:00 AM   5/14/2010 10:00 AM)

    Now I see the problem, your Query should be formulated as StartDate<=ScheduleDate and EndDate>=ScheduleDate. You got the signs opposite.

    If ScheduleDate is 05/13/2010 01:00 PM


    record 2 5/13/2010 01:00 PM   5/13/2010 02:30:00 PM
    Noye:That means if we are schedueling any assignment at 05/13/2010 at 01:00 PM it should return 1 record, because user is is out for 1 and half hours between this datetime range (5/13/2010 01:00 PM   5/13/2010 02:30:00 PM)

    Please review and let me know what I am missing?


    -Bin
    Friday, May 14, 2010 3:50 AM

All replies

  • Simple, when you set startdate and enddate both as 05/14/2010, you are actually setting your date range as a time point - 05/14/2010 12:00:00 AM. Neither schDate are at the time point. That's why you got nothing for the first setting.

    If you choose startdate as 05/14/2010, and enddate as 05/15/2010, the one with 5/14/2010 9:00:00 AM should show up.


    -Bin
    Thursday, May 13, 2010 9:49 PM
  • What does SPUtility.CreateISO8601DateTimeFromSystemDateTime return? The dates should be 2010-05-14T00:00:00Z and 2010-05-14T23:59:59Z respectively in order for that to work. Also, do you have your SPWeb datetime regional settings set to a different time zone than the SharePoint server?
    certdev.com
    Thursday, May 13, 2010 9:49 PM
  • Let me try to explain again. I am allowing user to select date from Sharepoint Date COntrol (only Date) and there will be pulldown  which contain "09:00 AM" and "01:00 PM" value.

    Then I am stoing value from Sharepoint DateTime Control as

    DateTime schDate = this.txtScheduleDate.SelectedDate; 

    Then I adding Hours depending on selected value as

    if (ddlInspectionTime.Text.Trim() == "9:00 AM")
    {
        schDate = schDate.AddHours(9);
    }
    else
    {
        schDate = schDate.AddHours(13);
    }

    Now I want to check  how many records exist in a Sharepoint List which has two date value i.e. Start Date and End Date fields (both are datetime field) and then comparing both the dates with new schDate using above mentioned query.

    @Bin_ - As you said I should choose startdate as 05/14/2010, and enddate as 05/15/2010, the one with 5/14/2010 9:00:00 AM should show up, thne what is the meaning for comparing dates. Let me know if you any other idea

    @Steve - I checked my regional setting in CA as well Site-Settings, on both the places it is common as "Easter Standard Time". Any Other place I need to check. When I use Response.Write(oQuery.Query); it shows as

    2010-05-23T13:00:00Z2010-05-23T13:00:00ZJohn Smith

    Another thing I like to mention that. data which I amentering entered into the list using custom aspx page (newform.aspx) from where I am adding date as

    item["Start Date"] = this.txtFrom.SelectedDate;
    item["End Date"] = this.txtEnd.SelectedDate;

    Please let me know what else I am missing?

    Friday, May 14, 2010 12:17 AM
  • Let me rephrase your situation:

    1. You have one parameter ScheduleDate that from the user, time could either be 9AM or 1PM.

    2. You have one list with StartDate, EndDate fields, both without time.

    3. You need a report/view to pull records from the list, according to your CAML, that StartDate>=ScheduleDate and EndDate<=ScheduleDate

    If I understand your situation correctly above, there are two questions here:

    1. How come StartDate>=EndDate? At least that is the logic in your query. I thought StartDate should always be earlier than EndDate.

    2. What are the StartDate, EndDate in your list, and ScheduleDate value as well? e.g.

     

      StartDate      EndDate
    record 1 5/14/2010      ?
    record 2 5/13/2010      ?

     

     

    ScheduleDate 1st time 5/14/2010 9:00:00AM    No record found
    ScheduleDate 2nd time ?

     

     


    -Bin
    Friday, May 14, 2010 3:16 AM
  • Bin

    Here is response of your queries in underlinebolf text

    1. You have one parameter ScheduleDate that from the user, time could either be 9AM or 1PM.  Yes

    2. You have one list with StartDate, EndDate fields, both without time. No we have the date and time both value

    3. You need a report/view to pull records from the list, according to your CAML, that StartDate>=ScheduleDate and EndDate<=ScheduleDate Yes

    If I understand your situation correctly above, there are two questions here:

    1. How come StartDate>=EndDate? At least that is the logic in your query. I thought StartDate should always be earlier than EndDate. No, Please check query again, I may be wrong. In plain text my query is StartDate >= ScheduleDate and EndDate <=ScheduleDate.

    2. What are the StartDate, EndDate in your list, and ScheduleDate value as well? e.g.

     If ScheduleDate is 05/14/2010 09:00 AM

                    StartDate                     EndDate
    record 1 5/14/2010 08:00 AM   5/14/2010 10:00 AM
    Note: That means if we are schedueling any assignment at 05/14/2010 at 09:00 AM it should return 1 record, because user is is out for two hours between this datetime range (5/14/2010 08:00 AM   5/14/2010 10:00 AM)

    If ScheduleDate is 05/13/2010 01:00 PM


    record 2 5/13/2010 01:00 PM   5/13/2010 02:30:00 PM
    Noye:That means if we are schedueling any assignment at 05/13/2010 at 01:00 PM it should return 1 record, because user is is out for 1 and half hours between this datetime range (5/13/2010 01:00 PM   5/13/2010 02:30:00 PM)

    Please review and let me know what I am missing?

    Friday, May 14, 2010 3:35 AM
  • Here is my reply in Italic:

    3. You need a report/view to pull records from the list, according to your CAML, that StartDate>=ScheduleDate and EndDate<=ScheduleDate Yes

    If I understand your situation correctly above, there are two questions here:

    1. How come StartDate>=EndDate? At least that is the logic in your query. I thought StartDate should always be earlier than EndDate. No, Please check query again, I may be wrong. In plain text my query is StartDate >= ScheduleDate and EndDate <=ScheduleDate.

    Let me explain how the logic works here:

     EndDate <=ScheduleDate also means ScheduleDate>=EndDate, which add back to the StartDate >= ScheduleDate, then means

    StartDate >= ScheduleDate >= EndDate, get rid of the ScheduleDate you get StartDate >= EndDate

    Note: ">=" can be passed on.     e.g.    5>=4>=3  means 5>=3

    2. What are the StartDate, EndDate in your list, and ScheduleDate value as well? e.g.

     If ScheduleDate is 05/14/2010 09:00 AM

                    StartDate                     EndDate
    record 1 5/14/2010 08:00 AM   5/14/2010 10:00 AM
    Note: That means if we are schedueling any assignment at 05/14/2010 at 09:00 AM it should return 1 record, because user is is out for two hours between this datetime range (5/14/2010 08:00 AM   5/14/2010 10:00 AM)

    Now I see the problem, your Query should be formulated as StartDate<=ScheduleDate and EndDate>=ScheduleDate. You got the signs opposite.

    If ScheduleDate is 05/13/2010 01:00 PM


    record 2 5/13/2010 01:00 PM   5/13/2010 02:30:00 PM
    Noye:That means if we are schedueling any assignment at 05/13/2010 at 01:00 PM it should return 1 record, because user is is out for 1 and half hours between this datetime range (5/13/2010 01:00 PM   5/13/2010 02:30:00 PM)

    Please review and let me know what I am missing?


    -Bin
    Friday, May 14, 2010 3:50 AM
  • Bin

    For Some records are it is working correctly, for some records it is not working correctly. Here is the query

    <Where>
     <And>
        <Leq>
         <FieldRef Name='Start_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"
             + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value>
       </Leq>
     <And>
       <Geq><FieldRef Name='End_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"
            + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value>
       </Geq>
       <Eq><FieldRef Name='QA_x0020_Employee' /><Value Type='Lookup'>" + strFullName + "</Value>
       </Eq>
      </And>
    </And>
    </Where>

    and Here is the result, why bold ecord is not coming in the result. Also notied record no 4, its nature same as record no 3, but it is not returning record. Please explain

    StartDateTime

    EndDateTime

    SchduleDate

    Result

    05/23/2010 08:00 AM

    05/23/2010 10:00 AM

    05/23/2010 09:00 AM

    Record Found

    05/22/2010 09:00 AM

    05/22/2010 12:00 AM

    05/22/2010 09:00 AM

    No Record

    05/25/2010 08:30 AM

    05/25/2010 09:30 AM

    05/25/2010 09:00 AM

    Record Found

    05/18/2010 12:30 PM

    05/18/2010 01:30 PM

    05/18/2010 01:00 PM

    No Record

     

    Friday, May 14, 2010 4:27 AM
  • I also notice that records values using CAML query builder how they are storing in the table, see the bold entry

    StartDateTime

    EndDateTime

    05/23/2010 08:00:00

    05/23/2010 10:00:00

    05/22/2010 09:00:00

    05/22/2010 00:00:00

    05/25/2010 08:30:00

    05/25/2010 09:30:00

    05/18/2010 12:30:00

    05/18/2010 13:30:00

     

    I am inserting datetime using my custom form as

    DateTime insertStartDate = this.txtFrom.SelectedDate;
    DateTime insertEndDate = this.txtEnd.SelectedDate;

    item["Start Date"] = insertStartDate;
    item["End Date"] = insertEndDate;

     

    Friday, May 14, 2010 4:40 AM
  • Bin

    For Some records are it is working correctly, for some records it is not working correctly. Here is the query

    <Where>
     <And>
        <Leq>
         <FieldRef Name='Start_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"
             + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value>
       </Leq>
     <And>
       <Geq><FieldRef Name='End_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>"
            + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value>
       </Geq>
       <Eq><FieldRef Name='QA_x0020_Employee' /><Value Type='Lookup'>" + strFullName + "</Value>
       </Eq>
      </And>
    </And>
    </Where>

    and Here is the result, why bold ecord is not coming in the result. Also notied record no 4, its nature same as record no 3, but it is not returning record. Please explain

     

    StartDateTime

    EndDateTime

    SchduleDate

    Result

    05/23/2010 08:00 AM

    05/23/2010 10:00 AM

    05/23/2010 09:00 AM

    Record Found

    05/22/2010 09:00 AM

    05/22/2010 12:00 AM

    05/22/2010 09:00 AM

    No Record

    05/25/2010 08:30 AM

    05/25/2010 09:30 AM

    05/25/2010 09:00 AM

    Record Found

    05/18/2010 12:30 PM

    05/18/2010 01:30 PM

    05/18/2010 01:00 PM

    No Record

     


    It is the AM/PM trick for hour format. 12:00 AM means midnight (link ):

    your record 2 has EndDateTime as midnight of 5/22/2010. If you mean noon 5/22/2010, it should be 5/22/2010 12:00 PM .

    I am not exactly sure about why record 4 is not showing. You might want to double check the date and time. Make sure they fit in the formula: StartDateTime <= ScheduleDate <= EndDateTime.


    -Bin
    Friday, May 14, 2010 12:32 PM
  • I also notice that records values using CAML query builder how they are storing in the table, see the bold entry

     

    StartDateTime

    EndDateTime

    05/23/2010 08:00:00

    05/23/2010 10:00:00

    05/22/2010 09:00:00

    05/22/2010 00:00:00

    05/25/2010 08:30:00

    05/25/2010 09:30:00

    05/18/2010 12:30:00

    05/18/2010 13:30:00

     

    I am inserting datetime using my custom form as

    DateTime insertStartDate = this.txtFrom.SelectedDate;
    DateTime insertEndDate = this.txtEnd.SelectedDate;

    item["Start Date"] = insertStartDate;
    item["End Date"] = insertEndDate;

     


    assigning insertStartDate doesn't specify how time is assigned. Is the SelectedDate a control that you can also choose time?

    -Bin
    Friday, May 14, 2010 12:38 PM
  • I have two SharePoint:DateTimeControl for from date and todat where DateOnly="false" that means it allows you date and time both. Earlier I was using 12 hours format, thne I have changed to 24 hours format  and retest again but getting same result as I mentioned earlier. Here is the modified table

    StartDateTime

     

     

    EndDateTime

     

     

    SchduleDate

     

     

    Result

     

     

    05/23/2010 08:00 AM

     

     

    05/23/2010 10:00 AM

     

     

    05/23/2010 09:00 AM

     

     

    Record Found

     

     

    05/22/2010 09:00 AM

     

     

    05/22/2010 12:00 PM

     

     

    05/22/2010 09:00 AM

     

     

    No Record

     

     

    05/25/2010 08:30 AM

     

     

    05/25/2010 09:30 AM

     

     

    05/25/2010 09:00 AM

     

     

    Record Found

     

     

    05/18/2010 12:30 PM

     

     

    05/18/2010 01:30 PM

     

     

    05/18/2010 01:00 PM

     

     

    No Record

     

     

    Let me know about your thoughts

    Friday, May 14, 2010 1:43 PM
  • There is a tool called U2U . Try use that tool on your server, it will parse the CAML query and get result from your list.

    -Bin
    Friday, May 14, 2010 3:54 PM
  • I am using same tool but their advanced version, which I downloaded from http://www.u2u.net/, I ceated query from this tool. Suprisingly the query suggested by you it is not returing those value which is returning by the code. I dont know why.

    Any idea

    Friday, May 14, 2010 4:13 PM
  • Let say we have record in our list where

    Start Date Time - 05/18/2010 12:30 PM and End Date Time 05/18/2010 01:30 PM, it is showing following CAML query with 1 record, but in our case it is not returning any record. Check the timestamp in the below query, because in our case if we see query its timestamp is different i.e. 2010-05-18T12:00:00Z2010-05-18T12:00:00Z. Please advise

    <Query>
       <Where>
          <And>
             <Geq>
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-28T08:14:13Z</Value>
             </Geq>
             <And>
                <Leq>
                   <FieldRef Name='End_x0020_Date' />
                   <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-28T08:15:12Z</Value>
                </Leq>
                <Eq>
                   <FieldRef Name='QA_x0020_Inspector' />
                   <Value Type='Lookup'>John Smithl</Value>
                </Eq>
             </And>
          </And>
       </Where>
    </Query>

    Friday, May 14, 2010 4:21 PM
  • Here is my code that how I am comparing the code

          SPSite currentLeaveSite = SPContext.Current.Site;
          SPQuery oQuery = new SPQuery();
          DateTime schDate = this.txtScheduleDate.SelectedDate;  
          
          if (ddlInspectionTime.Text.Trim() == "9:00 AM")
          {
            schDate = schDate.AddHours(9);
          }
          else
          {
            schDate = schDate.AddHours(13);
          }
          //Response.Write(schDate);
          
          
          oQuery.Query = "<Where><And><Geq><FieldRef Name='End_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value></Geq><And><Leq><FieldRef Name='Start_x0020_Date' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(schDate) + "</Value></Leq><Eq><FieldRef Name='QA_x0020_Employee' /><Value Type='Lookup'>" + strFullName + "</Value></Eq></And></And></Where>";
      
          SPWeb webLeave = currentLeaveSite.OpenWeb();
          Response.Write("<br>" + oQuery.Query);
            
          SPList leavelist = webLeave.Lists["Leave"];
          if (leavelist != null)
          {
    
            SPListItemCollection items = leavelist.GetItems(oQuery);
            Response.Write("<br>"+"Count => "+items.Count);
            if (items != null && items.Count > 0)
            {
    
              
              listItemCount = items.Count;
              Response.Write("<br>"+listItemCount.ToString() + " Records found");
              
            }
            else
            {
              Response.Write(listItemCount.ToString() + " Records found");
            }
            //=============
          }
    Friday, May 14, 2010 4:36 PM
  • <Query>
       <Where>
          <And>
             <Geq>    <Leq>
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-28T08:14:13Z </Value>   2010-05-18T09:00:00Z
             </Geq> </Leq>
             <And> Try get rid of this line for now
                <Leq> <Geq>
                   <FieldRef Name='End_x0020_Date' />
                   <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-28T08:15:12Z </Value> 2010-05-18T09:00:00Z
                </Leq> </Geq>
                <Eq> Try get rid of this line for now
                   <FieldRef Name='QA_x0020_Inspector' /> Try get rid of this line for now
                   <Value Type='Lookup'>John Smithl</Value> Try get rid of this line for now
                </Eq> Try get rid of this line for now
             </And> Try get rid of this line for now
          </And>
       </Where>
    </Query>
    -Bin
    Friday, May 14, 2010 4:37 PM
  • Here is the modified query as you suggested and run thru U2U, returning 1 record. When I checkeed in in code it is not returning anything for selected date 5/28/2010 1:00:00 PM

     <Query>
       <Where>
          <And>
             <Leq>
                <FieldRef Name='End_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:34Z</Value>
             </Leq>
             <Geq>
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:46Z</Value>
             </Geq>
          </And>
       </Where>
    </Query>

    Friday, May 14, 2010 4:48 PM
  • Here is the modified query as you suggested and run thru U2U, returning 1 record. When I checkeed in in code it is not returning anything for selected date 5/28/2010 1:00:00 PM

     <Query>
       <Where>
          <And>
             <Leq>
                <FieldRef Name='End_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:34Z</Value>
             </Leq>
             <Geq>
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T08:44:46Z</Value>
             </Geq>
          </And>
       </Where>
    </Query>


    You only got rid of the "QA Inspector" from the formula. Other criteria are still the same. The Geq and Leq were wrong in your query. I make change as follow (bold). Copy and paste the query in U2U, see if anything difference.

     <Query>
       <Where>
          <And>
             <Geq >
                <FieldRef Name='End_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T09:00:00Z </Value>
             </Geq >
             <Leq >
                <FieldRef Name='Start_x0020_Date' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2010-05-18T09:00:00Z </Value>
             </Leq >
          </And>
       </Where>
    </Query>


    -Bin
    Friday, May 14, 2010 5:07 PM
  • Bin

    Its rock, thanks a loooooot to you.

    I just wat to know what I was doing wrong?

    Friday, May 14, 2010 6:04 PM
  • Good to hear you got your problem solved. The key is:

    StartDate <= ScheduleDate <= EndDate              -------this is correct

    All you've been doing was:

    StartDate >= ScheduleDate >= EndDate              -------this is incorrect


    -Bin
    Friday, May 14, 2010 11:19 PM