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/

I got different results:

DECLARE @DATETIME DATETIME
 DECLARE @DATETIME2 DATETIME2(3)
 DECLARE @DATETIME2_PLUS DATETIME2(3)

SET @DATETIME2 = SYSUTCDATETIME()
 SELECT @DATETIME2

SET @DATETIME2_PLUS = DATEADD(millisecond, 1, @DATETIME2)
 SELECT @DATETIME2_PLUS

SET @DATETIME = CAST(@DATETIME2_PLUS AS DATETIME)
 SELECT @DATETIME
 /*
1. 2013-06-27 08:42:56.723                      2013-06-27 09:07:40.864
 2. 2013-06-27 08:42:56.724                     2013-06-27 09:07:40.865
 3. 2013-06-27 08:42:56.723                     2013-06-27 09:07:40.867
 */

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