SQL Server queries by time period

Baidu's information, save it:

When writing SQL statements that are queried by time periods, we usually write this query condition:

where date>= ' 2010-01-01 ' and date<= ' 2010-10-1 '.

However, when you execute SQL in real time, some statements are converted to this:

where date>= ' 2010-01-01 0:00:00 ' and date<= ' 2010-10-1:0:00:00 ', and then look at this condition, perhaps some will understand,

That is, "2010-10-1 0:00:00" after the data for example (' 2010-10-1:08:25:00 ') is not found, that is, 2010-10-1 data is not found.

You know why. You can modify the query condition to:

where date>= ' 2010-01-01 ' and date<= ' 2010-10-1 23:59:59 ' or where date>= ' 2010-01-01 ' and date<= ' 2010-10-2 '.

A table A field is a DateTime type with "Yyyy-mm-dd 00:00:00" stored



(1), such as data

2009-01-22 21:22:22
2009-01-22 19:21:11
2009-01-22 23:10:22


(2), with SELECT * from TABLE where date between ' 2009-1-22 ' and ' 2009-1-22 ', want to check the date of 2009-1-22 records, results found



(3), the cause of the problem

The default time for the short date type is 00:00:00, so when between is used as a constraint, it is equivalent to between ' 2009-1-22 00:00:00 ' and ' 2009-1-22 00:00:00 ', so the data is not found.



(4), Solution



--Plan One: date format conversion of the fields in the database

SELECT * from TB where convert (varchar (), riqi,120) = ' 2009-01-22 '


--Plan Two: give the date to complete the time and seconds

SELECT * from TB where Riqi between ' 2009-01-22 00:00:00 ' and ' 2009-01-22 23:59:59 '


-Results
/**//*
ID Riqi
---- ------------------------------------------------------
A 2009-01-22 21:22:22.000
B 2009-01-22 19:21:11.000
C 2009-01-22 23:10:22.000

(The number of rows affected is 3 rows)
*/

SQL Server queries by time period