Monday 9 June 2014

SQL Server datetime column missing milliseconds

Problem:
----------
DECLARE @objDateT datetime = '2014-01-29 10:25:17.121';
SELECT @objDateT AS '@objDateT'
--Result
--@objDateT
-------------------------
--2014-01-29 10:25:17.120
Root Cause:
-----------
Datetime values are rounded to increments of .000, .003, or .007 seconds
Solution:
--------
DECLARE @objDateTNew datetime2(3) = '2014-01-29 10:25:17.121';
DECLARE @objDateT datetime = @objDateTNew;
SELECT @objDateT AS '@objDateT', @objDateTNew AS '@objDateTNew';
--Result
--@objDateT @objDateTNew
------------------------- ------------------------
--2014-01-29 10:25:17.120 2014-01-29 10:25:17.121