score:0

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

score:0

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.

score:2

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

score:3

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)

More questions