Databases
StackOverflow2013
Postsdbo
POComparing date fields to "Today" using SharePoint search SQL?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComparing date fields to "Today" using SharePoint search SQL?
    text
    copied!<p>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."</p> <p>Specifically, I have the following part of a CAML query that uses :</p> <pre><code>&lt;Leq&gt;&lt;FieldRef Name="Article_x0020_Publish_x0020_Date"/&gt;&lt;Value Type="DateTime"&gt;&lt;Today /&gt;&lt;/Value&gt;&lt;/Leq&gt; </code></pre> <p>When attempting to convert that into SharePoint search SQL I was following the <a href="http://msdn.microsoft.com/en-us/library/ms443580.aspx" rel="nofollow noreferrer">microsoft documentation</a>; specifically the <a href="http://msdn.microsoft.com/en-us/library/ms477432.aspx" rel="nofollow noreferrer">DATEADD documentation</a>. I came up with this:</p> <pre><code>SELECT Title, ArticlePublishDate FROM SCOPE() WHERE "SCOPE" = 'Articles' AND ArticlePublishDate &lt;=DATEADD (DAY, 0, GETGMTDATE()) </code></pre> <p>However, when I use this query I get the following exception message:</p> <blockquote> <p>Name cannot begin with the '=' character, hexadecimal value 0x3D. Line 1, position 296.</p> </blockquote> <p>As an added twist, if you change the '&lt;=' operator to a '>=' operator then it accepts the query:</p> <pre><code>SELECT Title, ArticlePublishDate FROM SCOPE() WHERE "SCOPE" = 'Articles' AND ArticlePublishDate &gt;=DATEADD (DAY, 0, GETGMTDATE()) </code></pre> <p><strong>Is this a case of the Monday's? Is Microsoft messing with me? Or is this really broken...</strong></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload