Match all rows on particular day using LINQ to SQL

Question

How would you find all rows in a table that have a time matching a particular day?

The SQL DataType for the column Time is datetime.

For example, let's say you want all rows from 9/20/2014 and the table column Time looks like this...2014-09-20 17:02:05.903

var query = from t in SomeTable
            where t.Time // don't know what goes here
            select t;

Is your Time column `datetime` type? – joym8 Sep 25 '14 at 00:19 — joym8, Sep 25 '14 at 00:19
@greatbear302 Yes, the SQL DataType is `datetime` – Jonathan Kittell Sep 25 '14 at 00:21 — Jonathan Kittell, Sep 25 '14 at 00:21

score 4 · Accepted Answer · answered Sep 25 '14 at 00:14

You could try something like this one:

// Define your startDate. In our case it would be 9/20/2014 00:00:00
DateTime startDate = new DateTime(2014,09,20);

// Define your endDate. In our case it would be 9/21/2014 00:00:00
DateTime endDate = startDate.AddDays(1);

// Get all the rows of SomeTable, whose Time is between startDate and endDate.
var query = from t in SomeTable
            where t.Time>= startDate and t.Time<=endDate
            select t;

score 2 · Answer 2 · answered Sep 25 '14 at 00:37

2

void DoSomethingWithSomeTable(int day, int month, int year)
{
    var query = from t in SomeTable
                where t.Time.Date.Equals(new DateTime(year, month, day))
                select t;
}

answered Sep 25 '14 at 00:37

joym8

3,527
2
40
84

score 1 · Answer 3 · answered Sep 25 '14 at 00:13

1

 var query = from t in SomeTable
        where t.Time.Date == new DateTime(2014, 9, 20)
        select t;

answered Sep 25 '14 at 00:13

spender

112,247
30
221
334

score 1 · Answer 4 · edited May 23 '17 at 11:50

You could use extension methods to make it a little more readable:

public static class DateTimeExtensions
{
    public static bool InRange(this DateTime dateToCheck, DateTime startDate, DateTime endDate)
    {
        return dateToCheck >= startDate && dateToCheck < endDate;
    }
}

Now you can write:

dateToCheck.InRange(startDate, endDate)

or

var start = new DateTime(2014, 9, 20);
dateToCheck.InRange(start, start.AddDays(1));

(this solution was found posted here)

Match all rows on particular day using LINQ to SQL

4 Answers4