日期不能在将来的SQL [英] Date cannot be in the future SQL

问题描述

我正在尝试添加一种约束，以防止用户日后输入日期，我需要它在用户尝试这样做时引发错误。

I'm trying to add a kind of constraint that will prevent the user from entering dates in the future, I need it to raise an error when the user tries to do so.

这是我到目前为止：

Create Procedure CustomerBooks (@month int, @year int)
AS
BEGIN
    SELECT     
       SaleNumber, 
       month(saledate) as SaleMonth, year(saledate) as SaleYear,
       CustomerNumber, EmployeeNumber, SubTotal, GST, Total
    FROM         
       sale
    Where  
       month(saledate) = @month 
       and YEAR (saledate) = @year 
End

If salemonth > GETDATE(Month(saledate))  
   or saleyear > GETDATE(YEAR(saledate))
begin
   Raiserror ('Invalid entry, you cannot enter future dates',16,1)
end


EXEC dbo.CustomerBooks @month = '1', @year = '2012'

推荐答案

如果您使用SQL Server，最简单的解决方案是添加一个 CHECK CONSTRAINT ，以防止任何人进入超出（SQL Server）系统日期的日期。

If you are using SQL Server, the simplest solution would be to add a CHECK CONSTRAINT to prevent anyone entering a date beyond the (SQL Server) system date.

ALTER TABLE Sale ADD CONSTRAINT CKC_SALE_SALEDATE CHECK (SaleDate <= GetDate());

---

编辑1 em>关于OP对存储过程添加检查约束的评论

---

Edit 1 Regarding OP's comment on adding a check constraint to a stored procedure

CHECK CONSTRAINT 是它不能绕过而不禁用它。

The benefit of a CHECK CONSTRAINTis that it can't be bypassed without disabling it.

总是会有人插入/更新数据，而无需通过您设置的存储过程。限制将阻止输入错误的数据。

There will always be instances where someone inserts/updates the data without going through the stored procedure you've set up. The constraint will prevent entering incorrect data.

编辑2 关于OP的错误检查GetDate（）

以下结构目前不编译

   If salemonth > GETDATE(Month(saledate))  
   or saleyear > GETDATE(YEAR(saledate))

错误消息提示这里有什么错误，GetDate ）函数没有任何参数。很可能，我怀疑你打算写这样的东西

The error message hints to what is in error here, the GetDate() function doesn't take any parameters. Most likely, I suspect you meant to write something like this

   If salemonth > MONTH(GetDate())  
   or saleyear > YEAR(GetDate())

---

编辑3

验证输入不是将来可以通过使用以下if / then / else结构来完成。另一个选择是将输入转换为实际日期并检查。

Verify that the inputs are not in the future could be done by using following if/then/else construct. Another option would be to convert the inputs to an actual date and check on that.

IF (YEAR(GetDate()) < @year)    
  Raiserror ('Invalid entry, you cannot enter future dates',16,1)
ELSE IF (YEAR(GetDate()) = @year) AND (MONTH(GetDate()) < @month) 
  Raiserror ('Invalid entry, you cannot enter future dates',16,1)

SQL Fiddle示例

这篇关于日期不能在将来的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！