Submit-PnpSearchQuery Filter on Date

[NThoman]

$SearchQuery = "ReportDate=1900-01-01 Path:" + $SiteURL
Submit-PnPSearchQuery -Query $SearchQuery -All -SortList @{Created="Descending"}

How do you format the query string to return results with NULL dates in SharePoint on the field ReportDate?

[#2]

@NThoman What is the issue with search query you are using? Is it throwing any error or is it not returning the expected search results?

Try using date range once like below: 

ReportDate>=1900-01-01 AND ReportDate<=1900-01-01

Use correct managed property name of your date column.

References: 

1. SharePoint CSOM Query Keyword Filter On Date Time Type Column 
2. Filtering query string results by date range 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

I am not getting any results but there should be thousands. The field was first created with no spaces. The renamed with spaces.
ReporDate —> Report Date

[#4]

ReportDate>=1900-01-01 AND ReportDate<=1900-01-01 did not work. Got no results.

[#5]

@NThoman ReportDate is list/library level column or site level column?

I think you are trying to use the internal name of your list/site column - which does not work with search queries.

You have to use the name of managed property associated with your column. If you are using site column, managed property will be created automatically for you. But if you are using list/library level column, you have to use the existing managed property and map it with the crawled property associated with your list column.

Check: Showing list column in search results 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#6]

@ganeshsanap 

I am using the correct Internal Name

/sites/Reports/Reports/?newTargetListUrl=%2Fsites%2FReports%2FReports&FilterField1=ReportRun&FilterValue1=

FilterField1=ReportRun

$SiteURL = "https://GIVEITASHOT.sharepoint.com/sites/Reports"
$SearchQuery = "ReportRun<=1900-01-01 Path:" + $SiteURL

#Even tried using the mapped Refinable Feild I mapped.
#$SearchQuery = "RefinableDate01<=1900-01-01 Path:" + $SiteURL


Connect-PnPOnline -Url $SiteURL -Interactive

Write-Host "Searching for: $SearchQuery" -ForegroundColor Cyan
$SearchResults = Submit-PnPSearchQuery -Query $SearchQuery -All -SortList @{Created="Descending"}

$SearchResults

Give it a shot on your side. You need PowerShell 7 and the latest version of PnP installed.

[#7]

@NThoman 

You have to use the managed property associated with your list column, DO NOT use internal column name with SharePoint search.

Steps to map list column with managed property in SharePoint Online: How to sort by name with the Highlighted Content Web Part? 

Related documentations:

1. Overview of crawled and managed properties in SharePoint Online 
2. Manage the search schema in SharePoint 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#8]

I am using the internal name. It's why I create my fields without spaces first and then rename them later so you don't end up with all that extra junk. As you can see in the URL... I am using the internal name "ReportRun".

Search web parts work fine. I am trying to use PowerShell to find all the files with missing data so I can kick off a task to have Syntex re-process them so the field can get populated.

[#9]

@ganeshsanap 

I figured it out. You need to do a couple of things first..... Go into SharePoint Admin Center and set one of the RefinableDateXX fields to your custom date field in the Advance Search Settings. Then do a full index of the document library. Give this 24 hours. Once indexed you need to use the -Refiner switch. So find all files in the library and then refine the results for those with 1900-01-01 as the date (SharePoints Empty Date Value).

$SearchQuery = "(Path:" + $SiteURL +"/Reports/)"
$Refiner = "RefinableDate01(discretize=manual/1900-01-01)"

Connect-PnPOnline -Url $SiteURL -Interactive

Write-Host "Searching for: $SearchQuery" -ForegroundColor Cyan
$SearchResults = Submit-PnPSearchQuery -Query $SearchQuery -All -SortList @{Created="Descending"} -Refiners $Refiner

[#10 · Accepted solution]

Correction... that wasn't returning quite the right results also... however... this is:

$SearchQuery = "(NOT RefinableDate01>1900-01-01 Path:" + $SiteURL +"/Reports/)"

This was a useful resource:

https://www.techmikael.com/2023/04/filter-on-managed-properties-in-search.html