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 '18 at 18:58

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) 
| improve this answer | |

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)
| improve this answer | |
  • 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 '15 at 14:00
  • See my updated answer. You dont need to add Query – Amal Hashim Jun 4 '15 at 14:01
  • It is still getting all items. – omega Jun 4 '15 at 14:02
  • <Where><Lt><FieldRef Name='DocumentDate'/><Value Type='DateTime' IncludeTimeValue='False'>2008-01-01T00:00:00Z</Value></Lt></Where> – omega Jun 4 '15 at 14:03
  • Remove IncludeTimeValue='False' – Amal Hashim Jun 4 '15 at 14:04

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.