0

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?

0

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.

| improve this answer | |
  • Well, this did work.I added a ViewField to return the 'Title' column and in the foreach I write $item['Title'] but it is null. Why if there are no viewfields defined does it only return the ID column and when another column is defined in the viewfield, other than Created, it can't be displayed. Ihtought ViewFields grabbed those fields for the resultset! – Arale May 20 at 15:25

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.