title: SQL Query Notifications and GetDate()
body: <p>I am currently working on a query that is registered for Query Notifications.  In accordance w/ the rules of Notification Serivces, I can only use Deterministic functions in my queries set up for subscription.  However, GetDate()  (and almost any other means that I can think of) are non-deterministic.  Whenever I pull my data, I would like to be able to limit the result set to only relevant records, which is determined by the current day. </p>
  
  <p>Does anyone know of a work around that I could use that would allow me to use the current date to filter my results but not invalidate the query for query notifications?</p>
  
  <p>Example Code:</p>
  
  <pre><code>SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
  FROM dbo.bFiscalCalendar 
  WHERE fcDate &gt;= GetDate() -- This line invalidates the query for notification...
  </code></pre>
  
  <p>Other thoughts:</p>
  
  <p>We have an application controls table in our database that we use to store application level settings.  I had thought to write a small script that keeps a record up to date w/ teh current smalldatetime.  However, my join to this table is failing for notificaiton as well and I am not sure why.  I surmise that it has something to do w/ me specifitying a text type (the column name), which is frustrating.</p>
  
  <p>Example Code 2:</p>
  
  <pre><code>SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
  FROM dbo.bFiscalCalendar    
  INNER JOIN dbo.xApplicationControls ON  fcDate &gt;= acValue AND acName = N'Cache_CurrentDate' 
  </code></pre>
  
  <p>Does anyone have any suggestions?</p>
  
  <p>EDIT:  Here is a <a href="http://msdn.microsoft.com/en-us/library/ms181122.aspx" rel="nofollow noreferrer">link on MSDN</a> that gives the rules for Notification Services</p>