SharePoint FullTextSqlQuery Date and Time search is not accurate


You have implemented custom search using the FullTextSqlQuery API in SharePoint. Your requirement is to search for items/documents at a particular date and time. You use a custom field's (e.g., ows_orderdate) crawled property, set it up as a managed property (e.g., OrderDate) and use this managed property in the WHERE clause of your query. An example is shown below:

SELECT OrderAmount, OrderDate, Title , LastModifiedTime FROM SCOPE() WHERE CONTAINS(PATH,'""') AND OrderDate='2012-10-01 07:15:00'

You expect the result of this query to return two documents as there are only two documents that have the value '2012-10-01 07:15:00' for the OrderDate field.

In this scenario, if you have the OrderDate managed property set to be used in scopes, then the results from this query will return a few other documents including the correct one from that date and time you are searching for.

If you do not have the managed property set to be used in scopes, you will see the correct results.


This is an expected behavior when a Date Time managed property is marked as scoped and stored in the full-text index. The precision in this case will be to the hour, not minutes. The date time is broken apart and stored in the index up to the hour component. The reason for this is that the "scoped" optimization is directed at scenarios where the query is not very restrictive and potentially returns a large set of documents.


For more restrictive query, such as the one shown in the symptom part of this document, the property does not need to be marked as scoped and the property store can be used. Unfortunately, you cannot use both the modes, so you would need to choose what is the best one for the majority of your scenarios.

If the query with high restriction, such as the one shown in the symptom part of the document, returning correct result is what is the requirement, then do not set the managed property that is used in the WHERE clause of the query to be used in scopes.

Article ID: 2813380 - Last Review: Apr 26, 2013 - Revision: 1