SQL Query Date search using Between

Question

I just run this query

Select * 
from ProjectData 
where logtime between '2012-09-25 12:00:00.000' and '2012-09-25 12:59:59.999' 
order by LogTime

in an attempt to find the all record for 12 hour, We have record every second, so I was expecting 3600 record but to my surprise I got 3601 record and the last record time was

2012-09-25 13:00:00.000

Any idea why this record is picked? Even if Between includes the given values this value is above the condition. I am using SQL Server 2012 Express edition.

Answer 1

Try to use DATETIME2 datatype for logtime column -

Query:

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

Output:

logtime
---------------------------
2012-09-25 12:00:00.0000000
2012-09-25 12:59:59.9990000

DATETIME vs DATETIME2:

SELECT name, [precision]
FROM sys.types
WHERE name IN ('datetime', 'datetime2')

Output:

name        precision
----------- ---------
datetime2   27
datetime    23

Answer 2

You have taken Datetime as datatype and it has property of getting rounded.

Datetime values are rounded to increments of .000, .003, or .007 seconds. Details here
Eg:

SQL Fiddle

MS SQL Server 2012 Schema Setup:

Query 1:

Declare @testtime datetime = '2012-09-25 12:59:59.999'

select @testtime

Results:

|                         COLUMN_0 |
------------------------------------
| September, 25 2012 13:00:00+0000 |