SharePoint FullTextSqlQuery Date and Time search is not accurate

Article ID: 2813380 - View products that this article applies to.
Expand all | Collapse all

Symptoms

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,'"http://orders.contoso.com/OrdersList"') 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.

Cause

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.

Resolution

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.

Properties

Article ID: 2813380 - Last Review: April 26, 2013 - Revision: 3.0
Applies to
  • Microsoft Office SharePoint Server 2007
  • Microsoft SharePoint Server 2010
Keywords: 
KB2813380

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com