select statement using Between with datetime type does not retrieve all fields?

I'm facing a strange query result and I want to ask you why I'm facing this issue.

I store some datetime data into Tes开发者_开发百科tTable as following :

creation_time
-----------------------
2010-07-10 00:01:43.000
2010-07-11 00:01:43.000
2010-07-12 00:01:43.000

This table is created and filled as following :

create table TestTable(creation_time datetime);
Insert into  TestTable values('2010-07-10 00:01:43.000');
Insert into  TestTable values('2010-07-11 00:01:43.000');
Insert into  TestTable values('2010-07-12 00:01:43.000');

when I execute this query , I get two rows only instead of three as I expected:

SELECT *  FROM TestTable
WHERE  creation_time BETWEEN  CONVERT(VARCHAR(10),'2010-07-10',111) -- remove time part
                     and      CONVERT(VARCHAR(10),'2010-07-12',111) -- remove time part  

Or if I execute this query , the same issue ..

SELECT *  FROM TestTable
WHERE  CONVERT(datetime,creation_time,111) BETWEEN  CONVERT(VARCHAR(10),'2010-07-10',111) -- remove time part
                                              and      CONVERT(VARCHAR(10),'2010-07-12',111) -- remove time part

My Question :

• Why the last row ('2010-07-12 00:01:43.000') does not appear in the result even if I set the date range to cover all the day from 2010-07-10 to 2010-07-12?
• I use Sql server 2005 express edition with windows xp 32-bits.
• I'm trying to don't use a workaround solution such as increasing the date range to cover additional day to get the days I want.

Thanks .

You need to remove the time part from creation_time as well. Just use the same CONVERT if it works.

Currently you're asking if 2010-07-12 00:01:43.000 is less than 2010-07-12 00:00:00.000, which is not true.

it does not show the date because you have removed the time part, which would make the date equivalent to '2010-07-12 00:00:00.000' and since the last row is greater than this, so it is not displaying in the query results.

Your script should look like this:

SELECT * 
FROM TestTable
WHERE  creation_time BETWEEN
  convert(datetime, convert(char, '2010-07-10', 106))-- remove time part
  and **DATEADD**(day, 1, convert(datetime, convert(char, '2010-07-**11**', 106))) -- remove time part and add 1 day

This script will return all between 2010-07-10 00:00:00 and 2010-07-12 00:00:00. Basically this means all items created in 2 days: 2010-07-10 and 2010-07-11.

Converting columns in your table for comparison can be costly and cause indexes to not be used. If you have a million rows in your table and you have an index on creation_time, you will be doing an index scan and converting all million values to a string for comparison.

I find it better to use >= the start date and < (end date + 1 day):

SELECT *
FROM TestTable
WHERE creation_time >= '2010-07-10'
    AND creation_time < dateadd(day, 1, '2010-07-12')

And the reason your second one may not work is because format 111 uses slashes ("2010/07/10"), format 120 uses dashes ("2010-07-10"). Your converts aren't doing anything to your start and end date because you are converting a string to varchar, not a date. If you did this, it might work, but I would still recommend not doing the conversion:

SELECT * FROM TestTable
WHERE CONVERT(datetime, creation_time, 111) BETWEEN
    CONVERT(VARCHAR(10), CONVERT(datetime, '2010-07-10'), 111) -- remove time part
    and CONVERT(VARCHAR(10), CONVERT(datetime, '2010-07-12'), 111) -- remove time part

Date/time inclusive between 7/10/2010 and 7/12/2010:

SELECT *  FROM TestTable 
WHERE  creation_time BETWEEN  
 CONVERT(VARCHAR,'2010-07-10',101) -- remove time part                      
  and CONVERT(VARCHAR,'2010-07-13',101) -- remove time part