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>
                            <FieldRef Name="Created" />
                            <Value Type="DateTime"></Value>
                            <FieldRef Name="Expire" />
                            <Value Type="dateTime"></Value>
$SPListItems = $SPList.GetItems($spqQuery)
foreach ($splistitem in $splistitems) 
    Mod Expire....

Who can help me a little further?

Regards, Dimatrix

1 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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