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
精彩评论