In LINQ Query convert the string to datetime and check with today's date

c# datetime entity-framework linq

Question

DueDate is a varchar data type in Table. I now wish to compare the deadline with the current date.

 var query = (from o in db.Order_Reports
              where Convert.ToDateTime(o.ReportDueDateTime) >= DateTime.Now
              select o);

The mistake

The method "System.DateTime ToDateTime(System.String)" is not supported by LINQ to Entities, and so cannot be converted into a store expression.

How can I turn the string into a date and time and compare it to today's date?

1
1
1/10/2014 10:04:22 AM

Accepted Answer

I'm not sure how to put it into practice the manner you suggest, because

  1. The dates in string format can't be filtered by inequality using a SQL function (at least those that work with your date format and are convertable from LINQ to SQL query).
  2. Your database design has a fault that prohibits "normal" procedures from functioning.

There are, however, a few potential workarounds.

  1. Update the database to reflect the real datetime format for the date (best option, but of course sometimes unavailable)
  2. For the same result, write a stored procedure. You can use date parsing in the SQL code.
  3. Change the sentence as follows:

(code)

// Our required date
DateTime reportDate = new DateTime(2014,1,1).Date; 
// Let's find number of days between now and required day. Ensure that the date is not in the future!
int deltaDays = (DateTime.Now.Date - date).Days; 
// Let's get the list of dates which we need the reports for
var dates = Enumerable.Range(0, deltaDays + 1).Select(dd => DateTime.Now.Date.AddDays(-dd).ToString("MM/dd/yyyy")).ToArray();
// and query by this list
var query = (from o in db.Order_Reports
          where o.ReportDueDateTime in dates
          select o);

Although somewhat inefficient, this will serve the intended function without altering the DB. Consider it a temporary fix.

1
1/10/2014 10:35:04 AM

Popular Answer

String could be used. Compare.

var query = from o in db.Order_Reports
            where String.Compare(o.ReportDueDateTime, Now.ToString()) > 0
            select o;


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow