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

Question

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

score 1 · Accepted Answer · answered Mar 26 '11 at 08:53

1

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.

answered Mar 26 '11 at 08:53

Matti Virkkunen

63,558
9
127
159

I did that , but no result ? I'll update my question to mention this point . thanks – Abdullah Mar 26 '11 at 08:55
I found a method to remove the time part :http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server, Thanks matti and thank you guys , I will choose this answer since it's the first useful answer. – Abdullah Mar 26 '11 at 09:10

score 1 · Answer 2 · answered Mar 26 '11 at 08:55

1

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.

answered Mar 26 '11 at 08:55

Naveed Butt

2,861
6
32
55

score 1 · Answer 3 · answered Mar 26 '11 at 09:14

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.

Jason Goemaat · Answer 4 · 2011-03-26T10:02:08.867

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

score 0 · Answer 5 · answered Mar 26 '11 at 18:10

0

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

answered Mar 26 '11 at 18:10

Chris Gessler

22,727
7
57
83

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

5 Answers5