推荐SQL Server精准时间查询方式
USE Test /*插入或修改3条时间为以下极端情况的记录 UPDATE dbo.UserInfo SET AddTime = '2016-8-1 00:00:00' WHERE id=2 UPDATE dbo.UserInfo SET AddTime = '2016-8-1 23:59:59.005' WHERE id=3 UPDATE dbo.UserInfo SET AddTime = '2016-8-2 00:00:00' WHERE id=4 */ --三条记录如下 SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE id IN (2,3,4) --BETWEEN对比的是两侧<=(小于等于)或>=(大于等于),查询出来的结果会略显不精准 SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE AddTime BETWEEN '2016-8-1 00:00:00' AND '2016-8-2 00:00:00' --传统的大小于等于的方式,在59秒005毫秒的情况下是无法被查询出来的,就出现了数据错误的情况 SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE '2016-8-1 00:00:00' <= AddTime AND AddTime <= '2016-8-1 23:59:59' --对于时间要求较高的功能还是要使用两天之间0点的方式前面使用<=(小于等于)后面使用<(小于)第二天0点的方式 SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE '2016-8-1 00:00:00' <= AddTime AND AddTime < '2016-8-2 00:00:00'