SQL:在两个日期(开始和结束日期)之间选择 [英] Sql: Select between two dates ( start and end date)

问题描述

我不确定在哪里发布这个问题，我也希望我的措词正确.

我需要有关sql的帮助，下面的故事:

我有一张表，上面有开始和结束日期，包括时间(如时间表).一切正常.我有问题冲突.

假设我有带有以下信息信息的sql表:
StartDate:EndDate
2012年3月23日10:00 pm 2012年3月23日11:00 pm
2012年3月23日14:00 pm 2012年3月23日16:00 pm
2012年3月23日17:00 pm 2012年3月23日18:00 pm

我想添加一个没有冲突的事件-问题出在哪里！

假设新事件的开始日期相同，但时间为12:00至15:00
我预计会有冲突，因为结束时间(15:00)超过了14:00(第二行)

这是我做的
我检查了开始日期是否存在，例如
….
在哪里StartDate = @StartDate

…结果
2012年3月23日14:00 pm 2012年3月23日16:00 pm
2012年3月23日17:00 pm 2012年3月23日18:00 pm


如果我通过检查结束日期是否不在任何日期之间来延长
在哪里StartDate = @ StartDate
AND(((@ StartDate AND @EndDate OR EndDate之间的EndDate = @ EndDate OR EndDate> @ EndDate))

它可以正常工作，但是当StartDate在事件之间(比如说下午15点)时会出现另一个问题.解决这个问题已经花了几个小时...

这是我编写的过程，但仍然无法避免某些重复:

  SELECT  *  FROM  MyTable
  位置 StartDate =  @ StartDate 
     AND ((( @ EndDate   BETWEEN  StartDate  AND  EndDate  OR  EndDate =  @ EndDate   OR 结束日期>  @ EndDate ))

预先感谢您，我已经搜索了可能的解决方案，并无法找到比我更接近的解决方案.

解决方案

尝试以下方法，让我知道这是否满足您的要求. br/>

  SELECT  *  FROM  MyTable
  位置(((StartDate>  @ StartDate   AND  EndDate<  @ StartDate )- 开始于已经使用的时间段
    或
(StartDate<  @ EndDate   AND  EndDate>  @ EndDate ))- 在已使用的时隙内结束 

@Saral S斯大林，感谢您的帮助...您的解决方案可以进行这些细微调整

  SELECT  *  FROM  MyTable
 位置(((StartDate< =  @ StartDate   AND  EndDate>  @ StartDate )- 开始日期，以避免重复开始日期，并且在AND之后将>"更改为<"，但不在""之内
  或
  (StartDate<  @ EndDate   AND  EndDate> =  @ EndDate )))- 范围很完美，我只添加了EndDate，这样我们就不会有事件结束了同时. 

再次感谢您的解决方案...您为我提供了指导

简单&干净的演示转到
http://csharpdemos.blogspot.in/2012/09/how-to-select-all-dates-between-two.html 

I am not sure where to post this question, I also hope I worded it correctly.

I need help with sql, story below:

I have an table with starts and end date, including time(Like timetable). It’s working fine. I have issues conflicts.

Let’s say I have sql table with the below informa information:
StartDate: EndDate
23 March 2012 10:00pm 23 March 2012 11:00pm
23 March 2012 14:00pm 23 March 2012 16:00pm
23 March 2012 17:00pm 23 March 2012 18:00pm

I want to add an event with no conflict – that where the problem is!

Lets assume the new event has same start date, but time from 12:00 to 15:00
I am expecting a conflict as the end time (15:00) overlapse the 14:00 (second row)

Here is what I did
I checked if start date exists like
….
WHERE StartDate = @StartDate

…results
23 March 2012 14:00pm 23 March 2012 16:00pm
23 March 2012 17:00pm 23 March 2012 18:00pm


If I extend by checking if end date not between any dates
WHERE StartDate = @ StartDate
AND ((EndDate BETWEEN @ StartDate AND @EndDate OR EndDate = @ EndDate OR EndDate >@ EndDate))

It works but will have another issue when StartDate is between an event, let’s say 15pm. It''s been hours trying to solve this...

Here is the procedure I wrote, but still can''t avoid some duplicates:

SELECT * FROM MyTable
  WHERE StartDate = @StartDate
    AND ((@EndDate BETWEEN StartDate AND EndDate OR EndDate = @EndDate OR EndDate > @EndDate))

Thanking you in advance, I have googled possible solutions and cant fin one that closer to mine.

解决方案

Try below and let me know if this works for your requirement.

SELECT * FROM MyTable
  WHERE ((StartDate > @StartDate AND EndDate < @StartDate) -- Starts within an already used time slot
    OR 
(StartDate < @EndDate AND EndDate > @EndDate))-- Ends within an already used time slot

@Saral S Stalin, thanks for your assistance... your solution works with these minor adjustments

SELECT * FROM MyTable
 WHERE ((StartDate <= @StartDate AND EndDate > @StartDate) -- Included the start date to avoid duplicates start date and also changed the '>' to '<' after AND, it was not within
  OR 
  (StartDate < @EndDate AND EndDate >= @EndDate))-- The range was perfect, I just included the EndDate so that we dont have events ending at the same time.

Thanks once more for your solution... you gave me direction

Simple & clean demonstration goto
http://csharpdemos.blogspot.in/2012/09/how-to-select-all-dates-between-two.html

这篇关于SQL:在两个日期(开始和结束日期)之间选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！