Sql-server – Implement check constraint for varchar (7) to enforce YYYY-MM

check-constraintsregular expressionsql serversql-server-2016

I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM.

  • My intention is to create the column with data type: varchar(7)
    example value that would be saved from my front end: 2017-08

Would I then add a regular expression to enforce only numbers, dashes and max 7 in my check constraint setup? Or could I add some sort of regular expression to check for this pattern more exactly?

Best Answer

You could try concatenating a valid 'day' on to the YYYY-MM values and use a CASE expression and the ISDATE function.

IF EXISTS (
        SELECT *
        FROM dbo.TEST
        )
    DROP TABLE dbo.TEST
go
CREATE TABLE Test (
    ID INTEGER
    ,YYYY_MM_CHAR CHAR(7) NOT NULL
    ,CONSTRAINT chk_YYYY_MM_CHAR CHECK (
        CASE 
            WHEN ISDATE(YYYY_MM_CHAR + '-01') = 1
                THEN 1
            ELSE 0
            END = 1
        )
    );

INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-01')
INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-13')

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the CHECK constraint "chk_YYYY_MM_CHAR". The conflict occurred in database "master", table "dbo.Test", column 'YYYY_MM_CHAR'.
The statement has been terminated.
Related Question