Get data from database between specified date and time range using linq-sql

Question

I have a table with two separate columns for date and time (SQL Server 2008).

I am trying to get the data between 1 minute before current time and 1 minute after current current time in my asp.net MVC (C#) application.

I have tried to use the where condition as:

ce.Start_Date.Add(ce.Start_Time) >= _currDateTime.AddMinutes(-1) && 
ce.Start_Date.Add(ce.Start_Time) <= _currDateTime.AddMinutes(1)

But from the above where condition, it throws an error:

The datepart millisecond is not supported by 
  date function dateadd for data type date.

How can I correct/rectify this error?

Andrew Hare · Accepted Answer · 2009-10-20 12:35:50Z

2

Don't add the minutes in the where clause, try something like this instead:

for ce in data
let start = _currDateTime.AddMinutes(-1)
let end = _currDateTime.AddMinutes(1)
where ce.Start_Date.Add(ce.Start_Time) >= start && 
ce.Start_Date.Add(ce.Start_Time) <= end
select ce

LINQ to SQL is attempting to translate your where clause into valid T-SQL (using T-SQL's dateadd function) and it is having trouble doing so.

answered Oct 20, 2009 at 12:35

Andrew Hare

353k75 gold badges649 silver badges642 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prasad Over a year ago

I have tried the above condition too and its not allowing to add ce.Start_Date.Add(ce.Start_Time) in where condition or even using let

Devart · Accepted Answer · 2009-10-22 12:36:53Z

1

The problem seems to be with server DATE type which does not have proper SQL translation.
Try to make LINQ convert Date to DateTime, something like this:

 Convert.ToDateTime(ce.Start_Date).Add(ce.Start_Time)

answered Oct 22, 2009 at 12:36

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Prasad · Accepted Answer · 2009-11-23 06:08:48Z

1

Converted to .AsEnumerable() and manipulate the date and time and return as .AsQueryable();

answered Nov 23, 2009 at 6:08

Prasad

59.6k65 gold badges154 silver badges202 bronze badges

Comments

Sharique · Accepted Answer · 2009-10-20 13:13:06Z

0

Simplest way to add "update time" field in table and compare current time with this field.

answered Oct 20, 2009 at 13:13

Sharique

4,2296 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

Get data from database between specified date and time range using linq-sql

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related