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

c# datetime entity-framework linq

Question

In Table, DueDate data type is varchar. Now I want to check the due date with today's date

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

The error

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.

How am i suppose to convert the string to date and time and check with today's date

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

Accepted Answer

I don't know a way to implement it as you describe it, because

  1. There's no function in SQL that will filter the dates in string format by inequality (at least those that work with your date format and are convertable from LINQ to SQL query).
  2. You have a flaw in your database design which prevents "normal" methods from working

However, there are some possible workarounds

  1. Amend the database so that the date is of actual datetime format (best option, but of course sometimes unavailable)
  2. Create a stored procedure to achieve the same goal. In the SQL code, you can use date parsing
  3. Amend the query 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);

This will be a little inefficient, but achieve the purpose without changing the DB. Treat it as temporary solution.

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

Popular Answer

You could use String.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