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?

Answers


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

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.


Try this:

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

It should go much faster than the CONVERT option.


Here's my best answer to answering the original question:

Try this:

/* create a deterministic schema bound function */
CREATE FUNCTION FloorDate(@dt datetime)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN 
    RETURN CONVERT(datetime,  FLOOR(CONVERT(float, @dt)))
END
GO

To test, try the following. Please note the use of "PERSISTED" for the computed column and the use of [dbo.] when referring to the function

/*create a test table */
CREATE TABLE [dbo].[TableTestFloorDate](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestDate] [datetime] NOT NULL,
    [TestFloorDate]  AS ([dbo].[FloorDate]([TestDate])) PERSISTED,
 CONSTRAINT [PK_TableTestFloorDate] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 

You should now be able to add an index on the computed column (but see gotcha later)

CREATE INDEX IX_TestFloorDate ON  [dbo].[TableTestFloorDate](TestFloorDate)

Insert some random data as many times as you wish but more (1000+) is better if you wish to test index usage/execution plans

INSERT INTO TableTestFloorDate (TestDate) VALUES( convert(datetime, RAND()*50000))

Get the results

SELECT * FROM TableTestFloorDate WHERE TestFloorDate='2013-2-2'

Now here's the GOTCHA... The index that has been created on the computed column is not used! Instead, even when selecting data on the persisted field TestFloorDate, SQLServer (or at least my version) prefers an index on TestDate.

CREATE INDEX IX_TestFloorDate ON  [dbo].[TableTestFloorDate](TestDate)

I'm pretty sure (from memory) that indexes on computed, persisted columns are of benefit from a performance perspective - I guess you'll just have to try/test for your own specific usages

(Hope I've helped!)


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.


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)


Need Your Help

Hand Coded GUI Versus Qt Designer GUI

c++ qt user-interface rad qt-designer

I'm spending these holidays learning to write Qt applications. I was reading about Qt Designer just a few hours ago, which made me wonder : what do people writing real world applications in Qt use to