业务需求中，需要根绝开始时间和结束时间筛选一段时间内的任务存在个数。
示例图片
根据开始时间 9：00到 结束时间11：00 筛选信息
总共有这么四种情况可能出现
插入测试数据
CREATE TABLE [dbo].[Schedule] (
[ScheduleId] int NOT NULL IDENTITY(1,1) ,
[ScheduleTheme] nvarchar(100) NOT NULL ,
[ScheduleContent] nvarchar(500) NULL ,
[ScheduleBeginTime] datetime NULL ,
[ScheduleEndTime] datetime NULL ,
[CreateTime] datetime NULL
)
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-04 10:00:00', '2020-07-08 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-06 10:00:00', '2020-07-09 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-04 10:00:00', '2020-07-06 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-06 10:00:00', '2020-07-06 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-01 10:00:00', '2020-07-04 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('测试标题001', '', '2020-07-08 10:00:00', '2020-07-14 11:00:00', GETDATE());
示例LINQ 代码
var beginDate=new DateTime(“2020-07-07”);
var endDate=new DateTime(“2020-07-07”);
var tasks = demo.Where(n =>
(n.ScheduleBeginTime > endDate && n.ScheduleBeginTime > beginDate)||
(n.ScheduleBeginTime > beginDate && n.ScheduleEndTime < endDate) ||
(n.ScheduleEndTime < endDate && n.ScheduleEndTime > beginDate) ||
(n.ScheduleEndTime > endDate && n.ScheduleBeginTime < beginDate);
sql 示例
SELECT * FROM Schedule WHERE 1!=1
or (ScheduleBeginTime < '2020-07-07' AND ScheduleBeginTime > '2020-07-05') --查询1种情况
or (ScheduleBeginTime > '2020-07-05' AND ScheduleEndTime < '2020-07-07' ) --查询2种情况
or (ScheduleEndTime < '2020-07-07' AND ScheduleEndTime > '2020-07-05') --查询3种情况
or (ScheduleEndTime > '2020-07-07' AND ScheduleBeginTime < '2020-07-05') --查询4种情况
