Corey Roth [MVP]

You may have a case where you want to search for files that were recently modified.  Sure, you can use a CAML query to do this, but what if you want to look for recently changed files across multiple content sources?  Sure, you can write your own Full Text SQL Query and bind it in a web part, but there is an easier way.  The new FixedFullTextSqlQuery property in Release 2 of the Wildcard Search Web Part makes this really simple.  You just need the right query. 

Date functions in Enterprise Search SQL are similar but the syntax is a little different.  First you'll need to get the current date.  The GETGMTDATE function is what you need to get the current date.  Next you just need to use DATEADD to subtract the number of days, months, etc, that you need for your fixed query.  In this case, I'll subtract 7 days by specifying DAY and -7.  You can also specify MONTH, YEAR, HOUR, etc as well.  The last modified date is stored in a managed property called Write (took me a while to figure that one out).  That will make your condition look something like this.

WHERE Write > DATEADD(DAY, -7, GETGMTDATE())

Then, all you need to do is form your SELECT statement.  You can't use SELECT *, so you need to specify all of your columns individually.  The easiest way to get this column list is to look at the Select Columns property on your control.  My example below lists most of them.  When you are all done, this is what your query would look like.

SELECT Rank, Title, Path, Author, Write, WorkId, Size, Description, SiteName, CollapsingStatus, ContentClass, IsDocument, HitHighlightedSummary, HitHighlightedProperties, FROM Scope() WHERE Write > DATEADD(DAY, -7, GETGMTDATE())

This will query the entire index for documents modified in the last 7 days.  You may want to filter it down to a specific scope by using an additional condition in your WHERE clause.