CAML in Powershell within 2 dates

Question

I've been tweaking my code with various solutions discovered here and elsewhere with no luck so I figured its time to ask for help. I'm using Posh ver. 4.0 on SP 2013 Enterprise on-premises.

I have a sharepoint list from which I want to return the IDs of all items ont he list that were created between 2 dates.

    $Query = "<View><Query><Where><And><Geq><FieldRef Name=""Created"" /><Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" + [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($StartDate) + "</Value></Geq><Leq><FieldRef Name=""Created"" /><Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" + [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($EndDate) + "</Value></Leq></And></Where><OrderBy><FieldRef Name=""ID"" Ascending=""True"" /></OrderBy></Query></View>"

The output of the above query is:

<View>
<Query>
    <Where>
        <And>
            <Geq>
                <FieldRef Name="Created" />
                <Value Type="DateTime" IncludeTimeValue="TRUE">2018-10-26T00:00:00Z</Value>
            </Geq>
            <Leq>
                <FieldRef Name="Created" />
                <Value Type="DateTime" IncludeTimeValue="TRUE">2018-10-29T00:00:00Z</Value>
            </Leq>
        </And>
    </Where>
    <OrderBy>
        <FieldRefName="ID" Ascending="True" />
    </OrderBy>
</Query>

I have tried variations on this query including passing date only and setting the includetimevalue to false; using single quotes around the parameter values, enclosing Query with View adn not, and probably others which I am forgetting. I used the U2U applicatoin to create the query (which had single quotes and did not have the surrounding View tags.

Here is how I am creating the SPQuery object

    $Query = New-Object Microsoft.SharePoint.SPQuery
    $Query.Query = $Query
    $Query.ViewFields = "<FieldRef Name='ID' />"
    $Query.ViewFieldsOnly = $true

And getting them:

$ListItems = $SPList.GetItems($Query)

The result is all ids (1483 count) in the list. It should return less than 10. Additioally when I remove the ViewFields from the query, to get all fields back, and then try to display the Created column, it's null!

Also, I can run and get results back in U2U. I take the query output from the editor, stick it into powershell and run it on the server and I get results like theres not filter at all. It completely ignores the query it seems.

Ideas?

Answer 1

Here is a code sample to filter Created field in a range:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$startDate = Get-Date "12/17/2019" 
$startDatestring = $startDate.ToString("yyyy-MM-ddTHH:mm:ssZ");
$endDate = Get-Date "12/29/2019"
$endDatestring = $endDate.ToString("yyyy-MM-ddTHH:mm:ssZ"); 


$spweb = get-spweb http://sp/sites/devtest/ 
$splist = $spweb.Lists.TryGetList("List4") 
if ($splist) 
{ 
        $query = New-Object Microsoft.SharePoint.SPQuery;
        $query.Query = "<Where>  
                       <And>
                          <Geq>
                             <FieldRef Name='Created' /> 
                             <Value Type='DateTime'>$startDatestring</Value> 
                          </Geq> 
                          <Leq> 
                            <FieldRef Name='Created' /> 
                             <Value Type='DateTime'>$endDatestring</Value> 
                         </Leq> 
                        </And> 
                </Where>"

   $items = $splist.GetItems($query);
   Write-Host($items.Count) 
} 

For SPQuery.Query, directly pass Where tag like code demo above.