我刚刚运行此查询
Select * from ProjectData where logtime between '2012-09-25 12:00:00.000' and '2012-09-25 12:59:59.999' order by LogTime
为了找到12小时的所有记录,我们每秒都有记录,所以我期待3600记录,但令我惊讶的是我得到了3601记录,最后的记录时间是
2012-09-25 13:00:00.000
知道为什么选择此记录？即使Between包含给定值,该值也高于条件.我正在使用sql Server 2012 Express版.
解决方法
尝试将DATETIME2数据类型用于logtime列 –
查询：
DECLARE @temp TABLE (logtime DATETIME2)
INSERT INTO @temp (logtime)
VALUES
('20120925 12:00:00.000'),('20120925 12:59:59.999'),('20120925 13:00:00.000')
SELECT *
FROM @temp
WHERE logtime BETWEEN '2012-09-25 12:00:00.000' AND '2012-09-25 12:59:59.999'
ORDER BY logtime
输出：
logtime --------------------------- 2012-09-25 12:00:00.0000000 2012-09-25 12:59:59.9990000
DATETIME与DATETIME2：
SELECT name,[precision]
FROM sys.types
WHERE name IN ('datetime','datetime2')
输出：
name precision ----------- --------- datetime2 27 datetime 23
