当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL Server and BETWEEN

问题描述:

Possible Duplicate:

How can I determine in SQL Server if a dateTime range overlaps another

Hi,

I am trying to determine if a given start time and end time conflicts with another record. I am currently using the following query and if I get 1 or more results I know it has conflicted:

 SELECT

Sheet.SheetID,

Sheet.StartTime,

Sheet.EndTime,

FROM

Sheet

WHERE

AND @StartTime BETWEEN Sheet.StartTime AND Sheet.EndTime

OR @EndTime BETWEEN Sheet.StartTime AND Sheet.EndTime

This works well but really a user should be able to enter the same start time as a previous end time, ie. last jobs endtime was at 6am, new jobs start time was at 6am. The above query does not allow for this situation.

I have a feeling I may need to use greater than and less than rather than between but would be interested to hear any suggestions.

Thanks in advance

网友答案:

Using "greater(less) than" is better for performance so Microsoft recommends this clause. You should use it.

网友答案:

As you already figured out, the BETWEEN operator is inclusive (see documentation here).

I think you need to do the following:

SELECT
    Sheet.SheetID,
    Sheet.StartTime,
    Sheet.EndTime,
FROM
    Sheet
WHERE
    (@StartTime > Sheet.StartTime AND @StartTime < Sheet.EndTime)
    OR (@EndTime > Sheet.StartTime AND @EndTime < Sheet.EndTime)

Note that I didn't put in >= or <= operators, as this would prevent the 6am end/6am start situation you want to allow.

Edit Just had a thought - what if the new Sheet doesn't start or end within the bounds of an existing Sheet, but there is an entire other sheet contained within it? Can that happen? For example, you have one existing Sheet record which starts at 2am and ends at 4am. You try to insert a new one which starts at 1am and ends at 5am. Your query (and my original one) would allow this!

You'd have to amend it as follows:

SELECT
    Sheet.SheetID,
    Sheet.StartTime,
    Sheet.EndTime,
FROM
    Sheet
WHERE
    (@StartTime > Sheet.StartTime AND @StartTime < Sheet.EndTime)
    OR (@EndTime > Sheet.StartTime AND @EndTime < Sheet.EndTime)
    OR (@StartTime < Sheet.StartTime AND @EndTime > Sheet.EndTime)
网友答案:

Define 'conflicts with'...but if you mean 'overlaps', then the condition should be:

WHERE @StartTime < Sheet.EndTime AND @EndTime > Sheet.StartTime

You can choose '<=' or '>=' instead of '<' or '>' if you want exactly the same instant to count.

The condition checks that the value in @StartTime comes before the entry in Sheet ended, and that the value in @EndTime occurred after the entry in Sheet started. If both conditions are true, the entry in Sheet overlaps with @[email protected].

分享给朋友:
您可能感兴趣的文章:
随机阅读: