SQL Query Notifications and GetDate()

Question

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.

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?

Example 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 >= GetDate() -- This line invalidates the query for notification...

Other thoughts:

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.

Example Code 2:

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 >= acValue AND acName = N'Cache_CurrentDate'

Does anyone have any suggestions?

EDIT: Here is a link on MSDN that gives the rules for Notification Services

Answer 1

A good way to bypass this is simply to create a view that just says "SELECT GetDate() AS Now", then use the view in your query.

EDIT : I see nothing about not using user-defined functions (which is what I've used the 'view today' bit in). So can you use a UDF in the query that points at the view?

Answer 2

As it turns out, I figured out the solution. Basically, I was invalidating my query attempts because I was casting a value as a DateTime which marks it as Non-Deterministic. Even though you don't specifically call out a cast but do something akin to:

RecordDate = 'date_string_value'

You still end up w/ a Date Cast. Hopefully this will help out someone else who hits this issue.

This link helped me quite a bit.

http://msdn.microsoft.com/en-us/library/ms178091.aspx