在LINQ Query中将字符串转换为datetime,并检查今天的日期 [英] In LINQ Query convert the string to datetime and check with today's date
问题描述
在表中,DueDate数据类型为varchar。现在我想查看到期日期与今天的日期
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);
错误
LINQ to Entities不会识别方法'System.DateTime ToDateTime(System.String)'方法,并且此方法无法转换为存储表达式。
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
推荐答案
我不知道一种方法实现它,因为
I don't know a way to implement it as you describe it, because
- SQL中没有功能将通过不等式过滤字符串格式的日期(至少那些
- 您的数据库设计有一个缺陷,阻止正常的方法工作
但是,有一些可能的解决方法
However, there are some possible workarounds
- 修改数据库,日期是实际的日期时间格式(最佳选项,但当然有时不可用)
- 创建存储过程以实现相同的目标。在SQL代码中,您可以使用日期解析
- 修改查询如下:
(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.
这篇关于在LINQ Query中将字符串转换为datetime,并检查今天的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!