How do I avoid the use of getdate() in a SQL view?

Question

I'm writing a database view to sum up a bunch of records where the value in a date column is within the last 7 days. It looks something like this:

CREATE VIEW RecentRecordSum AS
SELECT      t.ID,   
            SUM(t.SomeValue) AS ValueSum
FROM        SomeTable t
WHERE       t.RecordDate >= DATEADD(d,-7,GETDATE())
GROUP BY    t.ID

Is there a way of doing this without having the GETDATE() directly in the where clause?

I'm using SQL Server 2000 and 2005.

Looking at the query plan shows that the cost of the getdate() call is only 0.03% of the entire query (which is considerably more complex than the one above), so performance is not an issue, however I like my queries to be deterministic.

Ideally I'd also like to expose the -7 parameter as a column so that it could be used in the where clause of something querying the view. Currently I'm contemplating a small number of views for 7, 14, 28 day windows.

Answer 1

One reason for your question might be to make the view more optimizable by removing the data transformation. Can't do it in a view, you'd need to make it a stored procedure and do the transform into a variable:

CREATE PROCEDURE RecentRecordSum AS

DECLARE @adate DATETIME

SELECT @adate = DATEADD(d, -7, GETDATE())

SELECT      t.ID,   
            SUM(t.SomeValue) AS ValueSum  
FROM        SomeTable t  
WHERE       t.RecordDate >= @adate  
GROUP BY    t.ID  

Answer 2

Another shot in the dark, like everyone else...

Perhaps you are wishing to make this an indexed view, which you would not be able to do with getdate(), since it is an indeterminate function. I have circumvented this in the past by calling getdate() from within another view that just contains

select getdate()

This level of indirection was enough to fool SQL Server 2000 and allow me to use schemabinding, but I can not guarantee this will work with later versions.