none
CAML filter between dates

    Pertanyaan

  • I'm working in a CEWP for MOSS07 (jquery features) and cannot return the correct sets of data.  I'm pulling from a custom list (imported from Access) with a column of date/time: date only.  I need to pull values where the date is between yesterday and a future date, say 5 days from now.  I've tried multiple combinations of <And> placements and cannot get anything.  The best I get is a set of values that are from yesterday on, but the recordset isn't limited by the <Leq> part.  I'm sure it is a syntax/ordering issue but I cannot peg it.  Anyone? 

    The 'gist' of the CAML is below...as I said, I've tried mixing up the different syntax (moving the <And>'s around and turning on & off the IncludeTimeValue flag...I've also switched back and forth between OffsetDays and Offset) but no luck.

    I have yet another <Where> that I'd like included (field 'ACTIVE', boolean, true) but I can work around that if the 3rd clause is the problem.  I just need the date range settled.

    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
    <listName>MOSSEMPLIST</listName> \
    <query> \
    <Query xmlns=''> \
    <Where> \
    <And> \
    <Geq> \
    <FieldRef Name='ANNIV' /> \
    <Value Type='DateTime' IncludeTimeValue='False'> \
    <Today OffSetDays='-1' /> \
    </Value> \
    </Geq> \
    <Leq> \
    <FieldRef Name='ANNIV' /> \
    <Value Type='DateTime' IncludeTimeValue='False'> \
    <Today OffSet='3' /> \
    </Value> \
    </Leq> \
    </And> \
    </Where><OrderBy> \
    <FieldRef Name='ANNIV' Ascending='False' /> \
    <FieldRef Name='Title' Ascending='True' /> \
    </OrderBy> \
    </Query> \
    </query> \
    <viewFields> \
    <ViewFields> \
    <FieldRef Name='Title'/> \
    <FieldRef Name='ANNIV' /> \
    <FieldRef Name='SENIORITY' /> \
    <FieldRef Name='YEARS' /> \
    <FieldRef Name='ACTIVE' /> \
    </ViewFields> \
    </viewFields> \
    </GetListItems> \
    </soapenv:Body> \
    </soapenv:Envelope>";
    **Clarification: If I run certain variations on the above I can get a set of data to return which is <Geq> yesterday but just keeps going ... I can also switch things around and get stuff that is <Leq> the "Today -1"  but never get the Today +3 to limit anything.


    If there is a unified theory in which the universe is programmed, my life is a divide-by-zero loop.


    • Diedit oleh Code_L 12 Juli 2012 1:23
    12 Juli 2012 1:19

Semua Balasan

  • I notice you have <Today OffSetDays='-1'> in the first clause, and <Today OffSet='3'> in the second clause.

    My guess is both should be OffSetDays...


    Sven De Bont - MCAD/MCTS http://blog.sdbonline.com

    12 Juli 2012 5:59
  • Sorry, in my frustration I pasted the last set I was trying to get to work...as mentioned in my post, I've tried combinations of both offset & offsetdays and no change.   The below code pulls anything greater than yesterday but doesn't stop at T+3:

    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
    <listName>MOSSEMPLIST</listName> \
    <query> \
    <Query xmlns=''> \
    <Where> \
    <Geq> \
    <FieldRef Name='ANNIV' /> \
    <Value Type='DateTime' IncludeTimeValue='False'> \
    <Today OffSetDays='-1' /> \
    </Value> \
    </Geq> \
    <And> \
    <Leq> \
    <FieldRef Name='ANNIV' /> \
    <Value Type='DateTime' IncludeTimeValue='False'> \
    <Today OffSet='3' /> \
    </Value> \
    </Leq> \
    </And> \
    </Where><OrderBy> \
    <FieldRef Name='ANNIV' Ascending='False' /> \
    <FieldRef Name='Title' Ascending='True' /> \
    </OrderBy> \
    </Query> \
    </query> \
    <viewFields> \
    <ViewFields> \
    <FieldRef Name='Title'/> \
    <FieldRef Name='ANNIV' /> \
    <FieldRef Name='SENIORITY' /> \
    <FieldRef Name='YEARS' /> \
    <FieldRef Name='ACTIVE' /> \
    </ViewFields> \
    </viewFields> \
    </GetListItems> \
    </soapenv:Body> \
    </soapenv:Envelope>";

     If I move the <And> to immediately after the <Where> I get nothing.


    If there is a unified theory in which the universe is programmed, my life is a divide-by-zero loop.


    • Diedit oleh Code_L 12 Juli 2012 11:54
    12 Juli 2012 11:27
  • I created a test list and a console application to do some tests.

    I created a List with a DateTime column called 'DateColumn' and added the dates for this month (July 2012):

    This is what I found:

    If you mess up the 'OffsetDays' part (e.g. use OffSet, Offset, OffSetDays, Offsetdays, ...), SharePoint will simply ignore the offset and use the value of Today.

    the syntax that worked for me, was OffsetDays='5' or OffsetDays='-5' (notice the capitalization!)

    Here's the output from my console app (>Today -5 and <Today + 5):

    To reproduce:

    1. Create a list named 'TestDates'
    2. Add a 'DateTime' column name 'DateColumn'
    3. Add dates for the month july 2012 (use datasheet view and drag/extend down
    4. (Also fill in the title column with the dates - it's required and it's the one that will be displayed by the console app)
    5. Create a console application and add a webservice reference to /_vti_bin/lists.asmx (I used a plain old 2.0 Web Reference and named it 'WebServices')
    6. Use the code below
    class Program
        {
            static void Main(string[] args)
            {
                WebServices.Lists serviceRef = new WebServices.Lists();
                serviceRef.Credentials= System.Net.CredentialCache.DefaultCredentials;
    
                string listName = "TestDates";
    
                XmlDocument xmlDoc = new System.Xml.XmlDocument();
                
                XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element,"Query","");
                XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element,"ViewFields","");
                XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element,"QueryOptions","");
                ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><DateInUtc>TRUE</DateInUtc>";
                ndViewFields.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='DateColumn'/>";
                
                ndQuery.InnerXml = "<Where><And><Gt><FieldRef Name='DateColumn'/><Value Type='DateTime'><Today OffsetDays='-5' /></Value></Gt><Lt><FieldRef Name='DateColumn' /><Value Type='DateTime'><Today OffsetDays='5' /></Value></Lt></And></Where>";
    
                
                XmlNode ndListItems = serviceRef.GetListItems(listName, null, ndQuery, ndViewFields, null, ndQueryOptions, null);
    
                Console.WriteLine("Todays date: {0}", DateTime.Today);
                Console.WriteLine();
                Console.WriteLine("Query: {0}", ndQuery.InnerXml);
                Console.WriteLine();
    
                XmlDataDocument xmlDocResult = new XmlDataDocument();
                xmlDocResult.LoadXml(ndListItems.InnerXml);
                XmlNodeList rows = xmlDocResult.GetElementsByTagName("z:row");
                foreach (XmlNode oNode in rows)
                {
    
                    Console.WriteLine(oNode.Attributes["ows_Title"].Value);
                }
    
                Console.ReadLine();
            }
        }
    HTH
     

    Sven De Bont - MCAD/MCTS http://blog.sdbonline.com

    17 Juli 2012 20:42