Posted by Erland Sommarskog on 12/09/05 10:16

manstein (jkelly.admin@gmail.com) writes:
> Your column is defined as datetime datatype and you have a default
> insert of the getdate() function with is also of datatype datetime
> there for your value is not stored in the format '10/12/2004 7:28:02
> AM' but instead is similar to: '2004-10-12 07:28:02.000'. I say
> similar because it depends on the precision of the milliseconds. What
> you need to do is format your where clause value to be compatable with
> datetime values:
>
> SELECT auditime
> FROM testtable
> WHERE convert(varchar(50),auditime,120) = '2004-10-12 07:28:02'

This may not be the best way. Now, it is not that likely that you index a
column like audittime. But assume that there had been an index and you
wanted it to be used. That would happen with the query above. As soon
as you put a column in a expression, SQL Server can no longer use the
index. (Or more precisely, it can no longer seek the index. The index
can still be scanned, that is read all of it.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]