My datetime2 comparison doesnt work where string does

Question

I get desired result back for the following statement.

select * from xx where  yyy='2014-05-17 19:00:00.2000000';

Why do i get no results back when i run?

DECLARE @NowDateTime datetime2(7) ='2014-05-17 19:00:00.2000000';
select * from xx where yyy= @NowDateTime

Answer 1

I had a similar issue with EF LINQ select having a parameter datetime2, while the column was datetime The issue probably was with datetime vs datetime2 rounding as described here.

We resolved the issue by altering the column datatype to datetime2.

alter table xx alter column yyy datetime2