使用 Linq to SQL 在 SQL 2008 中仅搜索日期的时间部分

发布于 2024-10-09 08:59:42 字数 1249 浏览 2 评论 0原文

我希望不仅可以按日期范围搜索，还可以按时间范围搜索日期时间字段。例如，“获取 5 月份下午 1 点到 3 点之间创建的所有记录”。我希望生成的 SQL 是：

WHERE CreatedOn BETWEEN '05/01/2010' AND '06/01/2010' AND CONVERT(time(4), CreatedOn) BETWEEN '01:00 PM' AND '03:00 PM'

我似乎无法弄清楚如何获取 lambda 表达式来实现这一目标。有人有任何线索吗？我的项目中有 System.Linq.Dynamic 。与将字符串作为 OrderBy 子句传递的方式类似，我想我可以使用Where 方法执行相同的操作，如下所示::

results = results.Where("CONVERT(time(4), OccurredOn) BETWEEN '{0}' AND '{1}'", Criteria.OffenseTimeStart, Criteria.OffenseTimeEnd);

但这会在 System.Linq.Dynamic 中引发异常。有人有任何指点吗？

感谢您的帮助！这是我的工作代码摘录。我将时间条件属性公开为 TimeSpans：

            if (Criteria.OffenseDateStart.HasValue)
                results = results.Where(o => o.OccurredOn >= Criteria.OffenseDateStart);

            if (Criteria.OffenseDateEnd.HasValue)
                results = results.Where(o => o.OccurredOn <= Criteria.OffenseDateEnd);

            if (Criteria.OffenseTimeStart.HasValue)
                results = results.Where(o => o.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart);

            if (Criteria.OffenseTimeEnd.HasValue)
                results = results.Where(o => o.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd);

原文

I want to be able to search on a DateTime field not only by a date range, but also by a time range. For example, "Get me all records in the month of May created between 1pm and 3pm". The SQL I'm looking to generated is:

WHERE CreatedOn BETWEEN '05/01/2010' AND '06/01/2010' AND CONVERT(time(4), CreatedOn) BETWEEN '01:00 PM' AND '03:00 PM'

I can't seem to figure out how to get a lambda expression to get me there. Anybody got any clue? I've got System.Linq.Dynamic in my project. Similar to the way you can pass a string as an OrderBy clause, I thought I might be able to do the same with the Where method as follows::

results = results.Where("CONVERT(time(4), OccurredOn) BETWEEN '{0}' AND '{1}'", Criteria.OffenseTimeStart, Criteria.OffenseTimeEnd);

But that throws an exception in System.Linq.Dynamic. Anybody got any pointers?

THANKS FOR THE HELP! Here is my working code excerpt. I exposed my time criteria properties as TimeSpans:

            if (Criteria.OffenseDateStart.HasValue)
                results = results.Where(o => o.OccurredOn >= Criteria.OffenseDateStart);

            if (Criteria.OffenseDateEnd.HasValue)
                results = results.Where(o => o.OccurredOn <= Criteria.OffenseDateEnd);

            if (Criteria.OffenseTimeStart.HasValue)
                results = results.Where(o => o.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart);

            if (Criteria.OffenseTimeEnd.HasValue)
                results = results.Where(o => o.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迟月 2024-10-16 08:59:42

我不认为你可以做“Between”，但你可以编写一个 linq 查询，例如：

where (x.CreatedOn.Date >= myDate.Date && x.CreatedOn.Date <= myDate2.Date)
   && (x.CreatedOn.TimeOfDay >= TimeSpan.Parse("13:00") && x.CreatedOn.TimeOfDay <= TimeSpan.Parse("15:00"))

I don't think you can do a 'Between', but you can write a linq query like:

where (x.CreatedOn.Date >= myDate.Date && x.CreatedOn.Date <= myDate2.Date)
   && (x.CreatedOn.TimeOfDay >= TimeSpan.Parse("13:00") && x.CreatedOn.TimeOfDay <= TimeSpan.Parse("15:00"))

回复收藏 0 原文

半边脸i 2024-10-16 08:59:42

像这样的东西吗？

results = results.Where(x => x.OccurredOn.Date >= Criteria.OffenseDateStart.Date
         && x.OccurredOn.Date <= Criteria.OffenseDateEnd.Date
         && x.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart.TimeOfDay
         && x.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd.TimeOfDay);

（假设 OccurredOn、OffenseDateStart、OffenseDateEnd、OffenseTimeStart 和 OffenseTimeEnd 均已输入作为日期时间。）

Something like this?

results = results.Where(x => x.OccurredOn.Date >= Criteria.OffenseDateStart.Date
         && x.OccurredOn.Date <= Criteria.OffenseDateEnd.Date
         && x.OccurredOn.TimeOfDay >= Criteria.OffenseTimeStart.TimeOfDay
         && x.OccurredOn.TimeOfDay <= Criteria.OffenseTimeEnd.TimeOfDay);

(Assuming that OccurredOn, OffenseDateStart, OffenseDateEnd, OffenseTimeStart and OffenseTimeEnd are all typed as DateTime.)

回复收藏 0 原文

~没有更多了~