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

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.

Answer 2

Try this:

CAST(FLOOR(CAST([datetime_column] as FLOAT)) AS DateTime)

It should go much faster than the CONVERT option.

Answer 3

Look at that question asked and answered by Cade Roux. Perhaps the solution would be to create a function using WITH SCHEMABINDING and then use it in the computed column

EDIT

I understand that you goal is to be able to have an index on that column.

If that cannot be done with a computed column then perhaps the only option would be to create an ordinary column and modify the data in that column each time you update the one it is based on. (say in trigger)

Answer 4

I'd suggest the somewhat simplier:

 cast(cast([datetime_column] as int) as datetime)

but I suspect you'll run into the same problem.

Now if the problem is in casting back to a datetime, you might want to consider using just cast([datetime_column] as int) as a separate field, just for the index.

Answer 5

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