Date Crawled properties Search SharePoint Online KQL Optional Date Filter if Date is null

CrawledDateMappedDateTime.JPGCrawled date fields from SharePoint Online are mapped to string properties which make it hard to apply a date filter on search results.

CrawledDate

If the filter EndDateOWSDATE is used in a Content Search Web Part, it won’t work as the mapped property EndDateOWSDATE  is of type text

-EndDateOWSDATE :1900-01-01..{Today}

Instead the crawled property ows_EndDate can be mapped to any out of box refinable date properties, i.e. RefinableDate00 and the filter will work.

-RefinableDate00:1900-01-01..{Today}

The filter above means returns all items with end date not within the past(i.e. not ended) . The negative (-) sign means exclude while the 1900-01-01..{Today} means between 1900-01-01 and Today’s  date.

You might be thinking just using

RefinableDate00>{Today}

will work.

The range option(..) together with the negative sign(-) is used to cater for the fact that end date can be null in which case ignore the filter. It means exclude all ended items without excluding items with no end date.

If today’s date need to fall between  start date and end date of items assuming start date field has been mapped to RefinableDate02 the following filter can be used

-RefinableDate01:1900-01-01..{Today-1} -RefinableDate02:{Today+1}..3000-12-31

Range .. Is used in the filter to ignore the condition is the date value is empty. The above filter will ignore all end dates ranging from 1900-01-01 to {today-1} and start dates ranging from {today+1} to 3000-12-3 but criteria will be ignored if date is empty or null.

 

 

 

 

Advertisements