SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 06:41 PM
| ||||
| ||||
 looking to collect distinct date part out of datetime field 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.      |
|
02-29-2008, 06:41 PM
| |||
| |||
| Re: looking to collect distinct date part out of datetime field 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 |
|
02-29-2008, 06:41 PM
| |||
| |||
| Re: looking to collect distinct date part out of datetime field sdowney717@msn.com (sdowney717@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, esquel@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 |
|
02-29-2008, 06:41 PM
| |||
| |||
| Re: looking to collect distinct date part out of datetime field http://www.aspfaq.com/show.asp?id=2464 shows all the outputs Was wondering though if it wont use an index on a convert. |
|
02-29-2008, 06:41 PM
| |||
| |||
| Re: looking to collect distinct date part out of datetime field 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
02-29-2008, 06:42 PM
| ||||
| ||||
| Re: looking to collect distinct date part out of datetime field Also refer this to know how to query on dates http://www.karaszi.com/SQLServer/info_datetime.asp Madhivanan |
Linear Mode
