Cédric Boivin Cédric Boivin - 9 months ago 69
C# Question

How to compare date with milliseconds with LINQ

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 Source

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);