SQL Server Brain Basher of the Week #006

Let's talk about date and time. Have a look on following code and try to guess the value returned by last SELECT statement.

USE tempdb;
GO

CREATE TABLE dbo.TestTable
(
 Code char(4) NOT NULL
 , TimeRecorded datetime NOT NULL
)
GO

INSERT INTO dbo.TestTable 
 (Code, TimeRecorded)
VALUES 
 ('E001', '12/31/2014 23:59:59:999');
GO

SELECT TimeRecorded FROM dbo.TestTable;

The date and time we entered is a date related to 2014 but if you run the last SELECT statement, it will show a date for 2015, it will show the value as 2015-01-01 00:00:00.000.

How this can be happened? It can happen based on the "milliseconds" added to the value. What we have to remember is the Accuracy of DateTime data type. Accuracy of the datetime type is rounded to increments of .000, 003, or .007 seconds, hence .999 is rounded off to .000 increasing the second by 1. This changes the entire datetime value inserted. That is why we see 2014 date as 2015.

Considering this fact, if your application needs the accuracy at millisecond level, datetime2  should be used instead of datetime.

One more thing to remember. If you write a query like below to get some records related to 13th of June 2014 from a table;

SELECT Id
FROM dbo.Table
WHERE Date BETWEEN '06/13/2014 00:00:00:000' 
   AND '06/13/2014 23:59:59:999';

You might get records related to 14th of June too. Again the reason is same. It can be overcome by changing the query as below;

SELECT Id
FROM dbo.Table
WHERE Date >=  '06/13/2014 00:00:00:000' 
   AND Date < ‘06/14/2014 00:00:00:000’;