If you are building a custom search solution based on SharePoint 2010 you might want to build your own custom query to query the SharePoint index. If you do you probably need to use the FullTextSqlQuery class, just like I am for my custom Birthday web part. While building that web part I ran into a couple of things that I might be able to spare others.

First thing to note is that the FullTextSqlQuery is available in two different namespaces. There is Microsoft.SharePoint.Search.Query.FullTextSqlQuery for SharePoint Foundation and there is Microsoft.Office.Server.Search.Query.FullTextSqlQuery for SharePoint Server. Be aware of which version of SharePoint you are running and use the right one, or your query won’t return the results you expect. So if you are running SharePoint Server you need to use Microsoft.Office.Server.Search.Query.FullTextSqlQuery.

Apart from this there don’t seem to be too many changes in the FullTextSqlQuery class. The SharePoint Search SQL Syntax for SharePoint2010 isn’t documented yet, but from what I saw I think Microsoft can just copy the documentation from the SharePoint 2007 version. I was particularly interested in the DATEADD clause. The DATEADD clause in SharePoint was developed to retrieve last modified documents within an environment. You could for example use WHERE LastModifiedTime <= DATEADD (DAY, -5, GETGMTDATE()), which would get you the documents modified within the last 5 days.

My use for the DATEADD clause in my birthday web part would be to retrieve the birthdays in the next few days. So I would want to use the DATEADD clause with a positive value, for instance 7 for the birthdays in the next week. Unfortunately the SharePoint Search SQL Syntax still doesn’t support using positive values in the DATEADD clause.
Luckily for me at least I can use LINQ now to run this query on the resultset I get back after running my FullTextSqlQuery. :-)