t-sqlgetdatedeterministicindexed-view

Deterministic function for getting today's date

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):

CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM lc.numbers
WHERE number<8

GO

CREATE UNIQUE CLUSTERED INDEX
idx_LCDates ON lc.vw_dates(SettingDate)

lc.numbers is simply a table with 1 column (number) which is incremented by row 1-100.

However, I keep getting the error:

Column 'SettingDate' in view 'lc.vw_dates' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I realize that GETDATE() is non-deterministic. But, is there a way to make this work?

I am using MS SQL 2012.

Edit: The hope was to be able to Convert GetDate() to make it deterministic (it seems like it should be when stripping off the time). If nobody knows of a method to do this, I will close this question and mark the suggestion to create a calendar table as correct.

Solution

  • The definition of a deterministic function (from MSDN) is:

    Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

    Note that this definition does not involve any particular span of time over which the result must remain the same. It must be the same result always, for a given input.

    Any function you can imagine that always returns the date at the point the function is called, will by definition, return a different result if you run it one day and then again the next day (regardless of the state of the database).

    Therefore, it is impossible for a function that returns the current date to be deterministic.

    The only possible interpretation of this question that could enable a deterministic function, is if you were happy to pass as input to the function some information about what day it is.

    Something like:

    select fn_myDeterministicGetDate('2015-11-25')

    But I think that would defeat the point as far as you're concerned.