如何插入带有日期验证的记录

发布于 2024-12-24 16:22:59 字数 749 浏览 1 评论 0原文

表1

ID  Month   datefrom   dateto     reason

001 12/2011 01/12/2011 10/12/2011 Leave
001 12/2011 18/12/2011 25/12/2011 Holiday
.....

datefrom、dateto数据类型为datetime，datefrom & dateto 格式为 (dd/mm/yyyy)

在插入 datefrom,dateto 之前，我想检查它是否与表中

尝试查询的 datefrom 和 dateto 匹配（在插入之前，我正在检查用户输入日期是否匹配与表）

SELECT * FROM table1 
WHERE (Month = '01/2012') AND 
      (id = '001') and 
      Convert(Datetime, '01/12/2011', 103)  between datefrom and dateto or 
      Convert(Datetime, '10/12/2011', 103)  between datefrom and dateto

如果我选择日期从 01/12/2011 到 10/12/2011，则上述查询有效，如果我选择日期31/11/2011 到 11/12/2011 意味着它允许插入...

如何修改我的查询....

需要查询帮助

原文

Table1

ID  Month   datefrom   dateto     reason

001 12/2011 01/12/2011 10/12/2011 Leave
001 12/2011 18/12/2011 25/12/2011 Holiday
.....

datefrom, dateto datatype is datetime, datefrom & dateto format is (dd/mm/yyyy)

Before inserting datefrom,dateto i want to check whether it is matching with datefrom and dateto from table

Tried Query (Before Inserting i am checking whether user entry date is matching with table)

SELECT * FROM table1 
WHERE (Month = '01/2012') AND 
      (id = '001') and 
      Convert(Datetime, '01/12/2011', 103)  between datefrom and dateto or 
      Convert(Datetime, '10/12/2011', 103)  between datefrom and dateto

The above query is working if i select the date from 01/12/2011 to 10/12/2011, incase if i selecting the date from 31/11/2011 to 11/12/2011 means it is allowing to insert...

How to modify my query....

Need query Help

分享到QQ

分享到微博

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

发布评论

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

无边思念无边月 2024-12-31 16:22:59

这是一个非常（非常）疯狂的猜测。您是否正在尝试INSERT到表中，但不希望[datefrom, dateto]间隔与表中的任何现有间隔交叉？

如果是，您可以这样做：

INSERT INTO table1
  (datefrom, dateto)
SELECT @DateFromNewValue, @DateToNewValue
WHERE NOT EXISTS
      ( SELECT *
        FROM table1
        WHERE datefrom <= @DateToNewValue
          AND @DateFromNewValue <= dateto
      )

并使用 'yyyy-mm-dd' 格式的任何 @DateNewValue 。

This is a very (, very) wild guess. Are you are trying to INSERT into the table but you don't want the [datefrom, dateto] intervals to cross with any existing interval in the table?

If yes, you could do this:

INSERT INTO table1
  (datefrom, dateto)
SELECT @DateFromNewValue, @DateToNewValue
WHERE NOT EXISTS
      ( SELECT *
        FROM table1
        WHERE datefrom <= @DateToNewValue
          AND @DateFromNewValue <= dateto
      )

and have any @DateNewValue in 'yyyy-mm-dd' format.

回复收藏 0 原文

~没有更多了~