Important point need to remember while working with DateTime filtering values in FullTextSqlQuery

  • Article

Once I was working with a custom search webpart issue in which search was implemented by using FullTextSqlQuery method, but it was not returning any results whenever we use contains predicate to filter the date time type columns.

There was a custom managed property of type Date Time to filter in the custom webpart. Since it was not returning any results in the custom webpart, we added that managed property in OOB advance search web part to include the custom managed property in the pick property section. After that, selected the date time managed property and executed the search by giving the same value that we given in the custom search webpart and it returned the correct results. We also checked the ULS logs to check the query used to execute the search with the date time type managed property in the filtering condition.

We saw that there is a difference in the construction of query between the custom webpart and the query executed in OOB (out-of-the-box).

SharePoint has only 3 filtering conditions with any managed property of type Date and Time. So, we must need to construct a query in a way that SharePoint can internally understand. If you pass ‘07/08/2009’ as your input date value SharePoint will convert that input date value in the format of ‘2009/07/08 18:30:00’ internally. You can see the query in the ULS logs once you execute a search in UI.

I have captured the SQL syntaxes for all the three filtering conditions. (eg: 07/08/2009 as our input date value)

Equal

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL from scope() where ("scope" = 'All Sites') And ((Created >= '2009/07/07 18:30:00' and Created < '2009/07/08 18:30:00'))

Earlier than

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL from scope() where ("scope" = 'All Sites') And (Created < '2009/07/08 18:30:00')

Later than

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL from scope() where ("scope" = 'All Sites') And (Created > '2009/07/08 18:30:00')

You can refer the above syntaxes for building your SQL query for filtering date type managed properties.