Posted by Erland Sommarskog on 03/12/06 12:52

sdowney717@msn.com (sdowney717@msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @val AND circdate < dateadd(DAY, @val, 1)


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

• Next in forum: Re: powerfull Oracle specifications PC
• Prev in forum: Re: Getting list of recently added IDENTITY items
• Thread view: Re: looking to collect distinct date part out of datetime field

[Reply to this message]