SQL Server: Floor a date in SQL server, but stay deterministic
(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?
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