0

I would like to use CAML Query to select records between a certain period (a particular month) and which where made by a certain user. Here's what it look like but it doesn't work

                 "<Where>"+
                    "<And>"+
                        "<And>"+
                            "<GE>"+
                                "<FieldRef Name='Date' />"+
                                "<Value Type='DateTime' IncludeTimeValue='False'>"+
                                SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtStart) + 
                                "</Value>"+
                            "</GE>"+
                            "<LT>"+
                                "<FieldRef Name='Date' />" +
                                "<Value Type='DateTime' IncludeTimeValue='False'>" +
                                SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtEnd) +
                                "</Value>" +
                            "</LT>"+
                        "</And>"+
                        "<Eq>" +
                            "<FieldRef Name='Author' />" +
                            "<Value Type='User'>" + currentUser.Name + 
                            "</Value>"+
                        "</Eq>"+
                    "</And>"+
                "</Where>" +
                "<OrderBy>"+
                    "<FieldRef Name='ClientName' Ascending='True' />"+
                    "<FieldRef Name='DateMonth' Ascending='True' />"+
                "</OrderBy>";

if i had to translate it into SQL command it would look similar to this:

SELECT * FROM myTable WHERE (myTable.Date BETWEEN <aStartDate> AND <aEndDate>) AND Author = <currentUser>

So basicaly in CAML it's greater than dtStart and lower than dtEnd it's how i tried to make a BETWEEN translated to CAML Query.

I'm using this with C# not javascript.

Did i miss something? Thanks in advance.

0

Few mistakes that I can figure out are, instead of <GE> and <LT>, you should use <Geq> and <Leq>...

Also while mentioning boolean True and False, make sure you use Caps like TRUE/FALSE, following is an example:

<Where>
    <And>
      <Geq>
        <FieldRef Name="StartDate" />
          <Value IncludeTimeValue="FALSE" Type="DateTime"> + SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtStart) + </Value>
      </Geq>
      <Leq>
        <FieldRef Name="StartDate" />
        <Value IncludeTimeValue="FALSE" Type="DateTime"> + SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtEnd) + </Value>
      </Leq>
    </And>
  </Where>

Reference

CAML Query Search Between Two Date Ranges : SharePoint

  • Simple mistake, i've didn't use the correct Name for my ref. And Geq and Leq worked corerctly. Thanks. – Timon May 7 '14 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.