SQL Server 2008 DateTime Range Query

Question

I have a table called Users :

 CreateTime UserName ======================================== 2012-08-30 14:23:12:000 zhang 2012-08-30 15:11:13:000 li 2012-08-30 16:32:22:000 wang 2012-08-31 11:23:12:000 steven 2012-08-31 12:05:14:000 zhao 2012-08-31 08:23:12:000 ddd 

and request:

 select UserName from Users where CreateTime >= '2012-08-30' and CreateTime <= '2012-08-31' 

So, the results should be 6 lines, but it is not.

How to solve this?

Using SQL Server Convert CONVERT(VARCHAR(23), CreateTime, 121) ?

Answer 1

It does not display 6 lines, because 2012-08-31 accepted by the interpreter as 2012-08-31 00:00:00 AM . Since you want to see the data before the 31st day, including explicitly , indicate the time or request for the date of the next day.

An example of using the date of the next day

 SELECT UserName FROM Users WHERE CreateTime >= '2012-08-30' AND CreateTime < '2012-09-01' 

Example with time clearly marked

 SELECT UserName FROM Users WHERE CreateTime >= '2012-08-30 00:00:00' AND CreateTime < '2012-09-31 23:59:59' 

Answer 2

All you need is CAST your CreateTime from Datetime to Date as follows:

 SELECT UserName FROM Users WHERE CAST(CreateTime as date)>='2012-08-30' AND CAST(CreateTime as date)<= '2012-08-31'; 

You can also use BETWEEN instead of <= and >= as follows:

 SELECT UserName FROM Users WHERE CAST(CreateTime as date) BETWEEN '2012-08-30' AND '2012-08-31'; 

See this SQLFiddle

Answer 3

The main problem is that when you simply enter a date, SQL Server interprets this date as midnight on that date. So, when you ask for "2012-08-31", it really means "2012-08-31 12:00 AM".

The best solution is to go one day for the day you want and use less than less or equal.

 select UserName from Users where CreateTime>='2012-08-30' and CreateTime < '2012-09-01' 

Answer 4

try it

 select UserName from Users where CreateTime>='2012-08-30' and CreateTime<'2012-09-01' 

Answer 5

CreateTime<='2012-08-31' will never be true for the last three dates, since their timestamps are after midnight. 2012-08-31 abbreviated for 2012-08-31 00:00:00 , so only the first three lines are returned.

You should always specify a timestamp - as well: using BETWEEN as suggested in other answers is a good idea.

Answer 6

 select UserName from Users where CreateTime>='2012-08-30' and CreateTime < '2012-09-01' 

Answer 7

You should use something like:

 SELECT UserName FROM Users WHERE DATEDIFF(d, '2012-08-30', CreateTime) >= 0 AND DATEDIFF(d, @ToDate, '2012-09-01') <=0 

You do not need to transfer hours and minutes with this request.