SQL2008R2: DATETIME2 TO DATETIME CAST ISSUE.

Datetime actually does not store milliseconds, it stores clock-ticks.

See more on the fascination topic: http://www.sqlusa.com/bestpractices/datetimeconversion/

Demo. Notice how it flips from 777 to 780, nothing in between:

DECLARE @dt2 datetime2(3) = '1900-01-01 00:00:00.777'; SELECT CONVERT(datetime, @dt2);
-- 1900-01-01 00:00:00.777

DECLARE @dt2 datetime2(3) = '1900-01-01 00:00:00.778'; SELECT CONVERT(datetime, @dt2);
-- 1900-01-01 00:00:00.777

DECLARE @dt2 datetime2(3) = '1900-01-01 00:00:00.779'; SELECT CONVERT(datetime, @dt2);
-- 1900-01-01 00:00:00.780

DECLARE @dt2 datetime2(3) = '1900-01-01 00:00:00.780'; SELECT CONVERT(datetime, @dt2);
-- 1900-01-01 00:00:00.780

BOL: "Use the time , date , datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time , datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications."

---

Kalman Toth Database & OLAP Architect sqlusa.com

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012