Clever Geek Handbook

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

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

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

I tried using the where clause like:

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

But from the above condition, it throws an error:

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

How can I fix / fix this error?

+2


source to share


4 answers


Converted to .AsEnumerable () and manipulates date and time and returned as .AsQueryable ();



+1


source


Don't add minutes to the sentence where

, try something like this:

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 trying to translate the clause where

into valid T-SQL (using the T-SQL dateadd

function ) and is having trouble doing it.

+2


source


The problem seems to be related to the DATE server type, which does not have proper SQL translation.

Try to do LINQ convert Date to DateTime, something like this:

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


source


The easiest way is to add a table to the "update time" field and compare the current time with this field.

0


source






More articles:


All Articles