CAML Query to find items with wrong expiry date

Question

I have a library with 65,000 items and for some items the “expiry date“ is not right. The field should actually be calculated automatically from the “creation date“ + 8 years. How can I determine the difference between creation date and “expiry date“ with a query so that I have only the relevant items in the array? Then change the value accordingly.

I did that already but so it doesn't work:

$spqQuery = New-Object Microsoft.SharePoint.SPQuery
$spqQuery.ViewAttributes = "Scope = 'Recursive'"
$spqQuery.RowLimit = $SPList.ItemCount
$spqQuery.Query = '<Where>
                    <And>
                        <Geq>
                            <FieldRef Name="Created" />
                            <Value Type="DateTime"></Value>
                            <FieldRef Name="Expire" />
                            <Value Type="dateTime"></Value>
                         </Geq>
                      </And>
                  </Where>'
$SPListItems = $SPList.GetItems($spqQuery)
foreach ($splistitem in $splistitems) 
{
    Mod Expire....
}

Who can help me a little further?

Regards, Dimatrix

Answer 1

As it looks it doesn't work directly with a CAML query but the following code does it also....

$weburl = "http://sp2016" 
$listname = "Testliste" 
$nameCreationField = "Created" # Internal Name Creation Field
$nameExpireField = "ExpDate" # Internal Name Expired Field
$difYears = 8 # Years

$web = Get-SPWeb -Identity $weburl
$list = $web.Lists[$listname]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewFields = "<FieldRef Name='$nameCreationField' /><FieldRef Name='$nameExpireField' />"
$camlQuery = '<OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy>'
$spQuery.Query = $camlQuery
#$spQuery.RowLimit = 100
$spListItems = $list.GetItems($spQuery)
$finalItems = @() 

foreach ($item in $spListItems)
{
    # Check if the CreatedDate plus years is less than the expire date-if yes, then add to the array
    if (($item[$nameCreationField]).AddYears($difYears) -lt $item[$nameExpireField])
    {
        #write-host "ID:" $item["ID"]
        #write-host "Created:" $item[$nameCreationField]
        #write-host "ExpDate:" $item[$nameExpireField]

        # Get item from list...
        $listItem = $list.GetItemById($item["ID"])
        # ... and add it to the destination array
        $finalItems += $listItem
    }
}