How to compare date with milliseconds with LINQ

Question

I try to make a query to my database with LINQ to SQL.

example :

var t = from a in context.A where a.date == myDate select a;

The problem is the date from SQL was return with millisecond, myDate variable contain de good milliseconds, but when i compare the date together the comparaison is not valide because the default output of myDate is MM/dd/yyyy HH:mm:ss and sql return MM/dd/yyyy HH:mm:ss:fff.

How i can get my date with the millisecond ?

Answer 1

It sounds like you want to compare your .NET datetime with precision milliseconds to the SQL datetime with precision milliseconds.

On testing, it looks like the SQL generated by LINQ To SQL has a defect: it rounds from its internal storage of ticks to a string representation improperly.

I've submitted this to Microsoft Connect: https://connect.microsoft.com/VisualStudio/feedback/details/589054 Try the repro!

To work around this defect where milliseconds in your DateTime are rounded improperly in the generated SQL, consider picking out each element of your date, and comparing each item (which is ridiculous, I know!):

DateTime d = DateTime.Parse("Jan 1 2010 09:44:33.0110");

var t = m.Msgs.Where(mm => 
                mm.CreatedOn.Value.Date == d.Date
             && mm.CreatedOn.Value.Hour == d.Hour
             && mm.CreatedOn.Value.Minute== d.Minute
             && mm.CreatedOn.Value.Second == d.Second
             && mm.CreatedOn.Value.Millisecond == d.Millisecond);

Answer 2

Is it the case that one of your dates has millisecond precision, and the other doesn't? In that case, I would be inclined to try something like this:

var t = from a in context.A where SqlMethods.DateDiffSecond(a.date, myDate) == 0 select a;

This page shows the DateTime methods/properties that are supported by Linq to SQL.