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?

Groot

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

Superlative

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.

Magdalenemagdalenian

getDate is non-deterministic I believe so that would be the reason – Exigent

But it is the mere default value. It is not part of the formula. BTW I get the same error for a default NULL column. – Groot

I guess the subroutine that does this doesn't distinguish between that part of he column definition that represents the formula, and the part that represents the default value... – Magdalenemagdalenian

Yes it is. I've been trying kristof's suggestion. – Groot

Answer 3

Try this:

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

It should go much faster than the CONVERT option.

Turgeon

CAST is non-deterministic for datetime values. – Groot

Answer 4

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!)

Hanforrd

I've no way to test it right now. Is this SQL Server 2000 compatible (I know it's ancient, but the tags in the question pointed out this version)? – Groot

The above was tested in Sql2012 but from memory I don't see why it wouldn't work on Sql2k in the exact manner described above – Hanforrd

Answer 5

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)

Exigent

Answer 6

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.

Dietz

The problem also is with casting from datetime (or convert()ing, for that matter). – Groot