looking to collect distinct date part out of datetime field

[sdowney717]

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.

[sdowney717]

SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices

[Erland Sommarskog]

sd********@msn.com (sd********@msn.com) writes:

SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
how does the '1' parameter affect the output as I know there are
several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sdowney717]

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

[Erland Sommarskog]

sd********@msn.com (sd********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Madhivanan]

Also refer this to know how to query on dates
http://www.karaszi.com/SQLServer/info_datetime.asp

Madhivanan