Comparing date fields to "Today" using SharePoint search SQL?

Question

I am converting some of the queries I have from CAML to SharePoint search SQL and ran across an issue when trying to compare my Date metadata fields to "Today."

Specifically, I have the following part of a CAML query that uses :

<Leq><FieldRef Name="Article_x0020_Publish_x0020_Date"/><Value Type="DateTime"><Today /></Value></Leq>

When attempting to convert that into SharePoint search SQL I was following the microsoft documentation; specifically the DATEADD documentation. I came up with this:

SELECT Title, ArticlePublishDate FROM SCOPE() WHERE "SCOPE" = 'Articles' AND ArticlePublishDate <=DATEADD (DAY, 0, GETGMTDATE())

However, when I use this query I get the following exception message:

Name cannot begin with the '=' character, hexadecimal value 0x3D. Line 1, position 296.

As an added twist, if you change the '<=' operator to a '>=' operator then it accepts the query:

SELECT Title, ArticlePublishDate FROM SCOPE() WHERE "SCOPE" = 'Articles' AND ArticlePublishDate >=DATEADD (DAY, 0, GETGMTDATE())

Is this a case of the Monday's? Is Microsoft messing with me? Or is this really broken...

Answer 1

Would removing the space between DATEADD and the '(' help? - the SQL parser may be treating it differently without the ( wedged up against it.

Answer 2

This is being treated as xml I suspect:

<=DATEADD (DAY, 0, GETGMTDATE()).

The rules of datatype precedence mean that xml is higher than all datetime values, so this may be why.

However, I'm sorry but I'm not familiar with either Sharepoint or CAML, so can't offer a fix.