SQL: Column Validation against GETDATE()

I set a constraint to check whether the column have the value of GETDATE() or not. The column rule is to accept dates开发者_如何学Go that are greater than or equal to GETDATE().

ALTER TABLE table1
ADD CONSTRAINT chk1 CHECK (date_column >= GETDATE ())

The problem is that, when I enter today's date into the column it gives an error indicating that it conflicts with the constraint otherwise if I enter tomorrow's date it accepts it. It seems to be that the (= sign) is not working.

My question is: what is the problem and how it can be solved?

If you are just entering today's date in the form mm/dd/yyyy that would evaluate to midnight for the current day. When you compare that to GetDate() it is evaluating to less than the current date and time You need to floor GetDate() to the start of the day.

Try using

cast(floor(cast(getdate() as float)) as datetime)

Most practical I think would be changing the constraint to this (for the reasons others pointed out):

ALTER TABLE table1
ADD CONSTRAINT chk1 CHECK (date_column >= CAST(GETDATE() as date))

Optionally you also can change the type of your column from datetime to date

You have to keep in mind that GETDATE() includes the current time as well. Your constraint appears to allow any value thats GreaterThanOrEqualTo the current Datetime stamp for which the value is evaluated. Which I would think in this case would be anything in the future. I'd like to see your client code that compensates for this. If your only looking to compare to the DATE then you need to change your constraint to the following.

CHECK (CONVERT(DATETIME, CONVERT(varchar(10), date_column, 101)) => CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101)))

Here's a quick example: SELECT GETDATE() 2011-04-26 20:39:11.240

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101)) 2011-04-26 00:00:00.000

GETDATE returns the current DATE AND TIME (down to milliseconds), NOT the current DATE.

SQL Server uses a Date-Time data type. For dates only (without time information), the time is defaulted to midnight (i.e. 00:00).

If you input today's date WITHOUT A TIME, it will default to midnight (i.e. 00:00). Today's date at midnight is always going to be earlier than GETDATE (which includes the current time).

Therefore your "today" date (midnight without time specificatoin) will never be >= GETDATE. Tomorrow at midnight, though, works because it is later than GETDATE.

Experiment: Specify today's date, PLUS A TIME (say one hour later). It should be accepted.