1

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?

3
  • 5
    Oh, and you’d be better off storing it as a date datatype using the first day of the month. Makes it easier to do comparisons & other operations
    – Philᵀᴹ
    Commented Aug 15, 2017 at 8:27
  • 3
    @BernardV, date will also require only 4 bytes of storage rather than 9 bytes (7-byte varchar value plus 2-byte field offset). You could also add a check constraint like DATEPART(day, DateColumn) = 1 to constrain the value to the first of the month.
    – Dan Guzman
    Commented Aug 15, 2017 at 11:43
  • I disagree with the suggestions to hack your values to squeeze them into a date field. Generally best to not "tell a lie" in your database structure. Some languages such as Java have a data-type to represent a year-month, such as the YearMonth class in Java. SQL lacks such a type. So the next best option is indeed to store a String of seven characters in the standard ISO 8601 format of YYYY-MM. This format happens to be chronological when sorted alphabetically. Commented Aug 16, 2017 at 7:06

1 Answer 1

3

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.
1
  • This will work provided they aren't using SET DATEFORMAT YMD (which may not be very likely but is still worth mentioning regardless, I think). Another thing is, this will probably not prevent them from using a different delimiter, so values like 2014.01 or 2014/01 will likely be permitted. (No idea if that's an issue but it's worth mentioning as well IMHO.)
    – Andriy M
    Commented Aug 15, 2017 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.