Thursday, May 27, 2010

Date Comparison in FullTextSqlQuery

I found this forum discussion.

Basically according to Steve Curran, the date literal must be surrounded by single quotes and formatted in YYYY-MM-DD format. Note this format does not address date with time value. However, even with this format, the = comparison does not work, for example, reportDate = '2010-05-24'. The >= comparison works.

In order to make equal comparison work, I had to combine > and < together. For example:

reportDate > inputDate.AddDays(-1).ToString("yyyy-MM-dd") and reportDate < inputDate.AddDays(1).ToString("yyyy-MM-dd")

The reportDate is a Date and Time field with Date Only format.

If you know any other approaches, please let me know.

Update: Also find this blog by about datetime comparison. Must read if you do datetime comparison in custom search web part.