SQL，在日期/时间之间选择 [英] SQL, Selecting between date/times

问题描述

这是（MS SQL Server 2008）它允许用户安排其他用户的开始和结束日期/时间没有重叠。

我正在寻址在时间表中无法重叠个人的业务规则。 E.G，如果用户1从2012年1月1日到2012年1月1日计划，则用户2不能将1/2/2012的请求提交到1/4/2012。这部分已经被照顾了，这里是我的SQL。

  SELECT * FROM fooTable 
 WHERE 
 Prim = 1 
 AND 
组= 10 
 AND 
（（'2012-06-01 08:01'在startdate和enddate之间
 OR'2012-06 -03 08:01'startdate和enddate之间）
 OR 
（'2012-06-01 08:01'< startdate）AND（'2012-06-03 8:01'> enddate ））
  

我现在有一个要求，允许一个时间表重叠，这意味着新的班次从最后一个开始。 EG，我的结束日期是8:00 pm的1/1/2012 - 我应该可以安排一个人在1/12/2012和下午8:00开始。

解决方案

首先，不要使用 Group 作为列名。它是每个SQL标准中的保留字。为了我的回答，我将其重命名为 grp 。

尝试从'2012-06-01 08:00' to '2012-06-03 08:00' ...

  INSERT INTO tbl（prim，grp，startdate，enddate）
 SELECT 1，10，'2012-06-01 08： 00'，'2012-06-03 08:00'
 WHERE NOT EXISTS（
 SELECT * 
 FROM tbl 
 WHERE prim = 1 
 AND grp = 10 
 AND'2012-06-03 08:00'> startdate  -  not> =允许共享边框
 AND'2012-06-01 08:00'< enddate  - 而不是BETWEEN ... AND 
）
  

请注意，我比较：

 
 new_ 结束> old_start 
 new_ 开始< old_end 
 

如果您使用 BETWEEN .. AND .. ，则包括测试中的移位边界。这与使用> = 和 <= 相同。您需要使用> 和< 允许边框重叠。

嗯，尝试我的大部分简化的语法。不知道你最近有什么。

这是一个在sqlfiddle上的工作演示.com 玩。

This is for (MS SQL Server 2008) It allows the user to schedule other users for a start and end date/time without overlaps.

I'm addressing the business rule of not being able to overlap individuals during a schedule. E.G, If user 1 is scheduled from 1/1/2012 to 1/4/2012, user 2 should not be able to submit a request for 1/2/2012 to 1/4/2012. This part is already taken care of, and here is my SQL.

SELECT * FROM fooTable
WHERE
    Prim = 1
AND 
    Group = 10
AND
    (('2012-06-01 08:01' between startdate and enddate
   OR '2012-06-03 08:01' between startdate and enddate)
OR
   ('2012-06-01 08:01' < startdate) AND ('2012-06-03 8:01' > enddate))

I now have a requirement to allow a schedule to overlap in the sense that the new shift can begin as the last one ends. E.G, My end date is 1/1/2012 at 8:00pm - I should be able to schedule someone to start at 1/1/2012 and 8:00pm.

Need some help thinking this one through.

解决方案

First off, don't use Group as a column name. It is a reserved word in every SQL standard. I renamed it to grp for the purpose of my answer.

Trying to schedule a new shift from '2012-06-01 08:00' to '2012-06-03 08:00' ...

INSERT INTO tbl (prim, grp, startdate, enddate)
SELECT 1, 10, '2012-06-01 08:00', '2012-06-03 08:00'
WHERE  NOT EXISTS (
    SELECT *
    FROM   tbl
    WHERE  prim = 1
    AND    grp = 10
    AND   '2012-06-03 08:00' > startdate -- not >= to allow sharing a border
    AND   '2012-06-01 08:00' < enddate   -- and not BETWEEN ... AND either
    )

Note that I compare:

new_end   > old_start
new_start < old_end

If you use BETWEEN .. AND .. you include the borders of a shift in your test. It's the same as using >= and <=. You need to use > and < to allow borders to overlap.

Well, and try my largely simplified syntax. Not sure about what you had there originally.
Here is a working demo on sqlfiddle.com to play with.

这篇关于SQL，在日期/时间之间选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！