Re: datetime
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 9 Dec 2005 08:16:54 +0000 (UTC)
manstein (jkelly.admin@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- datetime
- From: TGEAR
- Re: datetime
- From: Erland Sommarskog
- Re: datetime
- From: manstein
- datetime
- Prev by Date: Re: Regarding Index Rebuilding
- Next by Date: Please help the performance issue.
- Previous by thread: Re: datetime
- Next by thread: Database security
- Index(es):
Relevant Pages
|
