7

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sumit Gupta
  • 2,152
  • 4
  • 29
  • 46
  • 1
    Could it be that the acuracy of your datetime field in your table does not allow for such a fine distinction? I. e. can it really keep dates like `2012-09-25 12:59:59.999` and `2012-09-25 13:00:00.000` apart? May be you should limit your search by `2012-09-25 12:59:59` as the upper condition? – Carsten Massmann Aug 07 '13 at 05:18
  • I have datatype as DateTime and when I query the table it does shows me time in milliseconds. So I guess it does store them like this? OR – Sumit Gupta Aug 07 '13 at 05:20
  • 3
    `DATETIME` has an accuracy of 3.33ms - so in your case, your second value will be "rounded up" to `2012-09-25 13:00:00.000` since `DATETIME` can hold values ending in `.990`, `.993`, `.997` and `.000`. Either use `DATETIME2` instead (much higher precision!), or use `'2012-09-25 12:59:59.997'` for your second value if you're stuck on `DATETIME` – marc_s Aug 07 '13 at 05:23
  • @marc_s thanks for information, but does using Datetime2 effect performance as compare to datetime, as we have millions of record in our database. – Sumit Gupta Aug 07 '13 at 05:25
  • 1
    With `DATETIME2`, you can choose your accuracy as needed; `DATETIME2(3)` with three digits for fractional seconds (accuracy: 1ms) actually uses **less** storage space (7 bytes instead of 8) than `DATETIME` - so no, no negative performance effects to be expected – marc_s Aug 07 '13 at 05:48

3 Answers3

9

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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • based on your suggestion I ran Select * from Project_5_Data where Cast(logtime as DateTime2) between '2012-09-25 12:00:00.000' and '2012-09-25 12:59:59.999' order by LogTime and it shows correct results. So another question arise if DateTime doesn't support millisecond why it shows that in output? – Sumit Gupta Aug 07 '13 at 05:23
  • @Devart : Nice Answer. Can you please explain this behavior of DateTime ? – Ravi Singh Aug 07 '13 at 05:30
  • 3
    @Ravi Singh, each data-type have a precision limit. `DATETIME` has a lower precision, so server is forced use the rounding milliseconds. – Devart Aug 07 '13 at 05:44
4

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 |
Ravi Singh
  • 2,042
  • 13
  • 29
1

Try executing this query

SELECT CAST('2012-09-25 12:59:59.999' AS DATETIME)

This will outputs 2012-09-25 13:00:00.000 . So i think that is why your result contains the record with time 2012-09-25 13:00:00.000.

So actually it selects values between 2012-09-25 12:00:00.000 and 2012-09-25 13:00:00.000

Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138
  • It does, but why is my question... DateTime on my server does show millisecond so, it support that format and hence it should not round it off. – Sumit Gupta Aug 07 '13 at 05:21
  • 1
    actually it supports milliseconds but there is loss of precision while converting from string to datetime and it rounds values in increament of 0.000, 0.003, 0.007. See this link for more details http://msdn.microsoft.com/en-us/library/ms187819.aspx – Nitin Agrawal Aug 07 '13 at 05:35