How do I use LinqToSql to query on specific datetime

Question

When using Linq2Sql to query the database on a specific datetime, the query always comes back with no results. I suspect the times are off by nanoseconds causing the difference. The times must be equal but not that equal. How do I query the database for a datetime, down to just the second for precision?

The following code works but it excludes the time portion altogether:

        var Foos = Bars
                .Where(x =>
                    DbFunctions.TruncateTime(x.EndDate) == DbFunctions.TruncateTime(endDate))
                .Select(x => x.Id);

Also tried this code (which isn't pretty) but the compiler complained about having Date inside a linq query.

        var Foo = Bars
                .Where(x => 
                        x.EndDate.Value.Date == endDate.Value.Date &&
                        x.EndDate.Value.Hour == endDate.Value.Hour &&
                        x.EndDate.Value.Minute == endDate.Value.Minute &&
                        x.EndDate.Value.Second == endDate.Value.Second)
                .Select(x => x.Id);

Also tried this, but the query always came back empty. Possibly ToString() not working in the Linq query.

var foo = bars.Where(x => x.EndDate.ToString() == endDate.ToString())

I had to do something similar some time back and used http://stackoverflow.com/questions/1004698/how-to-truncate-milliseconds-off-of-a-net-datetime to work it out. I have not tested this with your example. — Guy Lowe, Feb 02 '16 at 01:10
Not sure this will work in a linq query, but I'll give it a shot — navig8tr, Feb 02 '16 at 01:13
I thought about it, but wouldn't I also have to check DiffMinutes, DiffHours, DiffDay, etc... — navig8tr, Feb 02 '16 at 01:21
No, you don't. calculating the seconds difference alone should be enough. — Yacoub Massad, Feb 02 '16 at 01:23
Are you using entity framework? are you using `EntityFunctions` or `DbFunctions`? — Yacoub Massad, Feb 02 '16 at 01:28

score 1 · Accepted Answer · answered Feb 02 '16 at 01:26

1

You can use the DiffSeconds method like this:

var Foo = Bars
       .Where(x => DbFunctions.DiffSeconds(x.EndDate, endDate) == 0)
       .Select(x => x.Id);

answered Feb 02 '16 at 01:26

Yacoub Massad

27,509
2
36
62

score 0 · Answer 2 · answered Feb 02 '16 at 02:24

A way around is to compare date part and hour part separately. Instead of trying to cast , lets divide and conquer

Suppose you have the data stored in DB as below

Pickup Time :10/9/2015 12:53:15 AM

and if you want to compare it present system time up to hours, the below query works

 orddb.order.Where(x.PickupDate.Hour >= DateTime.Today.Hour 
                    && x.PickupDate >= DateTime.Today)

How do I use LinqToSql to query on specific datetime

2 Answers2