SQL2008R2: DATETIME2 TO DATETIME CAST ISSUE.

Just for fun, try this one:

declare @date datetime;
select @date = '2011-10-01 00:00:00.000';
Select @date as Sure_000;
select dateadd(millisecond, -1, @date) as Not_999;
select dateadd(millisecond, -2, @date) as Not_998;
select dateadd(millisecond, -3, @date) as Now_997;

---

Have a nice day,

Christoph Muthmann

Microsoft SQL Server MVP - Blog