SQL Server Datetime Query Mismatch – Why Does My Query Search Datetime Not Match?

datetimesql server

select * 
from A 
where posted_date >= '2015-07-27 00:00:00.000' 
  and posted_date  <= '2015-07-27 23:59:59.999'

But the result contains a record that has posted_date today: 2015-07-28.
My database server is not in my country.
What is the problem ?

Best Answer

Since you are using datetime datatype, you need to understand how sql server rounds datetime data.

╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗
║   Name    ║ sn  ║        Minimum value        ║        Maximum value        ║ Accuracy ║  Storage  ║
╠═══════════╬═════╬═════════════════════════════╬═════════════════════════════╬══════════╬═══════════╣
║ datetime  ║ dt  ║ 1753-01-01 00:00:00.000     ║ 9999-12-31 23:59:59.997     ║ 3.33 ms  ║ 8 bytes   ║
║ datetime2 ║ dt2 ║ 0001-01-01 00:00:00.0000000 ║ 9999-12-31 23:59:59.9999999 ║ 100ns    ║ 6-8 bytes ║
╚═══════════╩═════╩═════════════════════════════╩═════════════════════════════╩══════════╩═══════════╝

enter image description here

Using below query, you can easily see the problem of rounding that sql server does when you use DATETIME data type.

select  '2015-07-27 00:00:00.000'                       as Original_startDateTime,
        convert(datetime ,'2015-07-27 00:00:00.000')    as startDateTime,
        '2015-07-27 23:59:59.999'                       as Original_endDateTime,
        convert(datetime ,'2015-07-27 23:59:59.999')    as endDateTime,
        '2015-07-27 00:00:00.000'                       as Original_startDateTime2,
        convert(datetime2 ,'2015-07-27 00:00:00.000')   as startDateTime2,  -- default precision is 7
        '2015-07-27 23:59:59.999'                       as Original_endDateTime2,
        convert(datetime2 ,'2015-07-27 23:59:59.999')   as endDateTime2     -- default precision is 7

enter image description here click to enlarge

DATETIME2 has been around since SQL Server 2008, so start using it instead of DATETIME. For your situation, you can use datetime2 with precision of 3 decimals e.g. datetime2(3).

Benefits of Using datetime2:

  • Supports up to 7 decimal places for time component vs datetime supporting only 3 decimal places .. and hence you see the rounding issue since by default datetime rounds the nearest .003 seconds with increments of .000, .003 or .007 seconds.
  • datetime2 is much more precise than datetime and datetime2 gives you control of DATE and TIME as opposed to datetime.

Reference :

Related Question