SharePoint Search Find Records between a date range

Question

Hello All,

This is my try to get records below the date:

SELECT Title, XXXX from scope() where "scope" ='XYZScope' AND XYZDate < 1/1/2007 12:00:00 AM

It says "Exception from HRESULT: 0x80040E07"

Please help.

Answer 1

You need to use the DATEADD operator. For example:

SELECT WorkId,Path,Title,Write,Author FROM Scope() WHERE XYZDate < DATEADD(DAY,30,GETGMTDATE())

Answer 2

Your dates need to be in a different format: ISO 8601

Example:

2008-10-13T14:05:31-05:00

I was never able to get the ESSQL to work with dates that had their time component still on but I have something working using the days.

Your query should be something like:

SELECT Title, XXXX from scope() where "scope" ='XYZScope' AND XYZDate < '2007-01-01'

Answer 3

try:

SELECT Title, XXXX from scope() where "scope" ='XYZScope' AND XYZDate <= '2007-01-01 12:00:00'

FullTextSqlQuery.Execute will always return nothing if you omit the time element. Another way may be to do something like

... XYZDate between date1 and '2007-01-01 12:00:00'

where date1 is a string represnetation of Date.MinValue

The culture (i.e. regional settings) of all servers in your farm also need to match in order for the statements to work.