MySQL set default value for DATE column using DATE_ADD?

Question

I'm trying to add a DATE column to my table with DEFAULT DATE value using an expression:

ALTER TABLE `wp_ezts_project_params` ADD `est_completion` DATE NOT NULL 
DEFAULT DATE_ADD( CURRENT_DATE(), INTERVAL 1 MONTH ) AFTER `client_id`

I have tried different variations of several SQL functions, but every time get an syntax error near DATE_ADD.

Are we not allowed to use expressions as default values in phpMyAdmin?

Answer 1

Are we not allowed to use expressions as default values in phpMyAdmin?

Yes, it's not allowed. It's explictly stated in the manual

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns

So if you really want to assign a value for this column you will a) need to pass that in or b) use a trigger

But really you don't need this column at all. It sounds like you are storing a value that's the result of a simple date add - storing it means you are introducing redundancy. Simply calculate it on the fly.