Error adding a check constraint

SQLScott · October 2nd, 2007, 03:33 PM

I am trying to add a simple check constraint and for the life of me I cannot figure out why SQL isn't accepting it. All i want to do is something like the following:

[ColumnName] < '1/1/2000'

But SQL won't accept this. I have even tried many different variations, such as

([ColumnName] < CONVERT([smalldatetime], '1/1/2000', (0)))

What am I completely brain farting on with this?

Thanks...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Peso · October 3rd, 2007, 04:05 AM

I think you already have data in the column that violates the check contraint.
Run this example code statement by statement

Code:

-- Create temporary table
CREATE TABLE    #Sample
        (
            dt DATETIME
        )

-- Insert some valid data
INSERT    #Sample
SELECT    '20071003'

-- Verify table data
SELECT * FROM #Sample

-- Create a "valid" check constraint
ALTER TABLE #Sample ADD CHECK (dt < '20080101')

-- Try to insert some invalid data
INSERT    #Sample
SELECT    '20080229'

-- Verify table data
SELECT * FROM #Sample

-- Try to create an "invalid" check constraint
ALTER TABLE #Sample ADD CHECK (dt <= '20061231')

-- Try to force an "invalid" check constraint!
ALTER TABLE #Sample WITH NOCHECK ADD CHECK (dt <= '20061231') 

-- Verify table data
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

Peso · October 3rd, 2007, 05:58 AM

Or use standard ISO datetime format?

[ColumnName] < '2000101'