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

SELECT CURRENT_TIMESTAMP

SELECT {fn NOW()}

---

I may have misunderstood the question, if you are just trying to move GetDate() and not replace it you can do the evaluation in a Having clause i.e.

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

Answer 2

If I'm understanding the question correctly, you could always try an inner join with a set that contains GETDATE() like in the following query:

SELECT      t.ID,   
            SUM(t.SomeValue) AS ValueSum
FROM        SomeTable t
INNER JOIN (SELECT DATEADD(d,-7,GETDATE()) AS MIN_DATE) MIN_DATE_SET
ON t.RecordDate >= MIN_DATE_SET.MIN_DATE
GROUP BY    t.ID

EDIT: I've looked at a query plan for a similar scenario, and they're identical. YMMV.

Answer 3

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 4

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.

Answer 5

Assuming that you have data in there for the most recent day, a subquery could maybe work:

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