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

One thought on “Date Crawled properties Search SharePoint Online KQL Optional Date Filter if Date is null

  1. Pingback: Date Crawled properties Search SharePoint Online FQL Optional Date Filter if Date is null | reshmeeauckloo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s