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 a query:

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

So, the results should be 6 rows, but it does not.

How to solve this?

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

Answer 1

It is not showing 6 rows because 2012-08-31 is received by the interpreter as 2012-08-31 00:00:00 AM. Since you want to see data up to and including the 31st, you can either explicitly mention the time or query the next day's date.

Example Using the Next Day's Date

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

Example with Time Explicitly Mentioned

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 to CAST your CreateTime from Datetime to Date like this:

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 >= like this:

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

See this SQLFiddle