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

Need Your Help

how update source on standalone computer with hg?

mercurial

I use mercurial on a standalone computer. I have also made some small changes of the source code on this computer. Now I want to update this code with a new version that I can bring to this compute...

Why we should always return values from a function?

php function return

I am not a big programming guy but have listen from programmers a lot of times that we should always return values from a function. I want to know the reason.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.