mysql Check if date is greater than 0000-00-00

Question

I would like my orderedDate column (using the date type) to not allow null or 0000-00-00

I've set the orderedDate column as not null, but when using insert into

INSERT INTO Purchase(custName, price)
VALUES('Max Wong', 10.00)

Ordered date is 0000-00-00. I assume this is because it is the default value when no other value is given.

I've also tried

ALTER TABLE Purchase
ADD CHECK (orderedDate > '0000-00-00');

(also orderedDate > 0000-00-00 and orderedDate > 0)

And I read to try

SET mysql_mode = 'NO_ZERO_DATE'

But it didn't work.

I've seen a lot of php suggestions when looking online but I have to use sql.

If it were me .. I'd run a query to replace all zero dates with a "standard" date .. Even one that predates your business. `1970-01-01` for instance ... Then you should mark that column as default `CURRENT_TIMESTAMP` or something of the like. Once this is completed .. You can simply use `WHERE date BETWEEN DATE(NOW()) AND DATE(1970-01-01)` — Zak, Nov 05 '17 at 17:59
You may want to look up how to use a trigger in MySQL that works as a check constraint: https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint — Thorsten Kettner, Nov 05 '17 at 21:19

score 2 · Accepted Answer · answered Nov 05 '17 at 18:02

MySQL does not support CHECK constraints. It parses them, so it doesn't throw an error, but it doesn't save them or enforce them.

If you enable the NO_ZERO_DATE SQL mode, MySQL gives a warning, not an error, if you use 0000-00-00.

If you enable the NO_ZERO_DATE mode and strict mode, then it gives an error.

This mode is the default starting with MySQL 5.7.

1 Answers1