Entity SQL compare datetime without milliseconds

Question

I'm trying to fetch some records from MSSQL DB using EntityObject with EntitySQL query. The field i'm using to filter is type of datetime. The generated query projected without millisecond, to the SQL Server returns nothing. When i'm adding the milliseconds, i get results.

How can i use the EntitySQL to get result without the milliseconds?

thanks.

Answer 1

It's not elegant, but this worked for me:

foos.SingleOrDefault(f => f.EntryDate.Year == bar.EntryDate.Year &&
                          f.EntryDate.Month == bar.EntryDate.Month &&
                          f.EntryDate.Day == bar.EntryDate.Day &&
                          f.EntryDate.Hour == bar.EntryDate.Hour &&
                          f.EntryDate.Minute == bar.EntryDate.Minute &&
                          f.EntryDate.Second == bar.EntryDate.Second);

Answer 2

One way to do it is to create a view of your data, where your datetime column is converted to smalldatetime (which does not have milliseconds).

Then add the view to your entity framework model, and read the data through the view.

Hope this helps

Shiraz

Answer 3

Workaround I found is on initial fetch store date as .ToBinary() then when you filter just do new DateTime(binaryValue) and compare with that.