Hi friends,
I have a field call [Connected Date], data type(smalldatetime)
I want to sort the data's between two dates (01/08/2006 - 01/10/2006)
i use following
SELECT * FROM Localfile WHERE [Connected Date] between CONVERT(varchar(25),19/10/2006) and CONVERT(varchar(25),19/10/2006)

but i'm getting following error
Syntax error converting character string to smalldatetime data type.



surendran
(Anything is Possible)
http://ssuren.spaces.msn.com

__________________
surendran
(Anything is Possible)
http://www.suren.info
http://ssuren.spaces.msn.com

Have you checked your date format, is correct in your select statement?

Jaime E. Maccou

Surendran,

First, your conversion is incorrect... since you didn't put any quotes around the dates, you end up with a division problem instead of a date answer. Second, the dates in your WHERE clause don't even come close to what you listed in your description. Third, never use BETWEEN because of the times associated with dates.

To get the data between 01/08/2006 and 01/10/2006 as you requested, then you have to do it like this...

 SELECT *
   FROM LocalFile
  WHERE [Connected Date] >= '01/08/2006'
    AND [Connected Date] < '01/11/2006'

Notice the second date is 1 more than you wanted... that's so all times on 01/10/2006 will be included. You can't use BETWEEN because it would include midnight on 01/11/2006 and you don't want that.

The BIG reason for doing this is so that the [Connected Date] in the WHERE clause remains "sargable" so that if an index is present, it can be used.

--Jeff Moden