I want to compare dates, but my code isn't working.

    $spQuery = New-Object Microsoft.SharePoint.SPQuery;
    $camlQuery = "<Query><Where><Lt><FieldRef Name='DocumentDate'/><Value Type='DateTime' IncludeTimeValue='False'>$expireyDate</Value></Lt></Where></Query>";
    $spQuery.Query = $camlQuery
    $sourceItems = $sourceList.GetItems($spQuery)

and the value of $expireyDate is 1/1/2008. I want to get all items who's field value 'DocumentDate' (which is a date field) is less than the expiry date.

It just returns all the items in the list, and doesn't filter.

Does anyone know what's wrong?

  • How can I do that with another date column and not with a static date?
    – Dimatrix
    Sep 19, 2018 at 18:58

2 Answers 2


One other thing important to know is that you will have to include the following property in your query:


And here is a script example:

$web = get-spweb http://site.domain.com/sites/yoursite
$SPList = $web.lists["yourList"]

$now = get-date
$oneHourAgo = $now.addhours(-1)

$spqQuery = New-Object Microsoft.SharePoint.SPQuery
$spqQuery.ViewAttributes = "Scope = 'Recursive'"
$spqQuery.RowLimit = $SPList.ItemCount

$spqQuery.Query = '<Where><And><And><Geq><FieldRef Name="Modified" /><Value Type="DateTime" IncludeTimeValue="True">' + [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($oneHourAgo) + '</Value></Geq><Eq><FieldRef Name="Some_Status" /><Value Type="Text">Created</Value></Eq></And><Lt><FieldRef Name="Modified" /><Value Type="DateTime" IncludeTimeValue="True">' + [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($now) + '</Value></Lt></And></Where>'

$SPListItems = $SPList.GetItems($spqQuery)

foreach ($splistitem in $splistitems) 

You should create date in ISO format (Ex: -2015-06-04T02:32:20Z as below

$expireyDate = [microsoft.sharepoint.utilities.sputility]::CreateISO8601DateTimeFromSystemDateTime($expireyDate);

$spQuery = New-Object Microsoft.SharePoint.SPQuery;
$camlQuery = "<Where><Lt><FieldRef Name='DocumentDate'/><Value Type='DateTime' IncludeTimeValue='False'>$expireyDate</Value></Lt></Where>";
$spQuery.Query = $camlQuery
$sourceItems = $sourceList.GetItems($spQuery)
  • I tried it, result was <Query><Where><Lt><FieldRef Name='DocumentDate'/><Value Type='DateTime' IncludeTimeValue='FALSE'>2008-01-01T00:00:00Z</Value></Lt></Where></Query> but it still got all documents.
    – omega
    Jun 4, 2015 at 14:00
  • See my updated answer. You dont need to add Query Jun 4, 2015 at 14:01
  • It is still getting all items.
    – omega
    Jun 4, 2015 at 14:02
  • <Where><Lt><FieldRef Name='DocumentDate'/><Value Type='DateTime' IncludeTimeValue='False'>2008-01-01T00:00:00Z</Value></Lt></Where>
    – omega
    Jun 4, 2015 at 14:03
  • Remove IncludeTimeValue='False' Jun 4, 2015 at 14:04

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.