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?
Converted to .AsEnumerable () and manipulates date and time and returned as .AsQueryable ();
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.
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)
The easiest way is to add a table to the "update time" field and compare the current time with this field.