<p>(This is related to <a href="https://stackoverflow.com/questions/85373/floor-a-date-in-sql-server">Floor a date in SQL server</a>.)</p>

<p>Does a deterministic expression exist to floor a DATETIME? When I use this as a computed column formula:</p>

<pre><code>DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0)
</code></pre>

<p>the I get an error when I place an index on that column:</p>

<blockquote>
  <p>Cannot create index because the key column 'EffectiveDate' is non-deterministic or imprecise.</p>
</blockquote>

<p>But both DATEDIFF and DATEADD are deterministic functions by definition. Where is the catch? Is it possible?</p>

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:
The function is schema-bound.
All built-in or user-defined
functions called by the user-defined
function are deterministic.
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.
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.