Prakash Prakash - 4 months ago 8
SQL Question

My SQL ADDDATE FUNCTION is not working in CREATE TABLE

I want to create a table in which there is

OPENDATE
field. The default value for which should be 30 days earlier to system date.

CREATE TABLE bugs (
ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
TITLE VARCHAR(20) NOT NULL,
SEVERITY INT NOT NULL check(SEVERITY>0 && SEVIRITY<5),
OPENDATE DATETIME NOT NULL DEFAULT ADDDATE(CURRENT_TIMESTAMP,-30),
CLOSEDATE DATETIME DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;






  1. This gives error at
    ADDDATE
    function. Can I not use functions at the table creation time? If no, what's the solution, and If yes, why this is not working?

  2. I also noticed that the identifier
    CURRENT_DATE
    is not recognized. Why?


Answer

This isn't possible! See the following explanation from official MySQL docs:

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.

http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

You have to control the default value of the column on software side. After creation and initializing the table with some data, you can UPDATE the column with such data.