SQL Server: Floor a date in SQL server, but stay deterministic

Question

(This is related to Floor a date in SQL server.)

Does a deterministic expression exist to floor a DATETIME? When I use this as a computed column formula:

DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0)

the I get an error when I place an index on that column:

Cannot create index because the key column 'EffectiveDate' is non-deterministic or imprecise.

But both DATEDIFF and DATEADD are deterministic functions by definition. Where is the catch? Is it possible?

Answer 1

My guess is that this is a bug of some sorts. In SQL 2005 I was able to create such an indexed view without a problem (code is below). When I tried to run it on SQL 2000 though I got the same error as you are getting.

The following seems to work on SQL 2000, but I get a warning that the index will be ignored AND you would have to convert every time that you selected from the view.

CONVERT(CHAR(8), datetime_column, 112)

Works in SQL 2005:

CREATE TABLE dbo.Test_Determinism (
    datetime_column DATETIME    NOT NULL    DEFAULT GETDATE())
GO

CREATE VIEW dbo.Test_Determinism_View
WITH SCHEMABINDING
AS
    SELECT
        DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) AS EffectiveDate
    FROM
        dbo.Test_Determinism
GO

CREATE UNIQUE CLUSTERED INDEX IDX_Test_Determinism_View ON dbo.Test_Determinism_View (EffectiveDate)
GO

Answer 2

Does your column [datetime_column] have a default value set to "getDate()" ??

If so, since getdate() function is non-deterministic, this will cause this error...

Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:

1. The function is schema-bound.
2. All built-in or user-defined functions called by the user-defined function are deterministic.
3. The body of the function references no database objects outside the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.
4. The function does not call any extended stored procedures.

User-defined functions that do not meet these criteria are marked as nondeterministic. Built-in nondeterministic functions are not allowed in the body of user-defined functions.