MYSQL not accepting NOW() as a default value for a datetime field

Question

I am attempting to set the default value of a datetime field to NOW() in MySQL but the MySQL error says it is invalid?

/* create the invoice table */
CREATE TABLE IF NOT EXISTS INVOICE
(
    LINE_ID INT(9) NOT NULL AUTO_INCREMENT,
    INVOICE_ID INT(9) NOT NULL,
    QUANTITY INT(9) NOT NULL DEFAULT '1',
    INVOICE_DATE DATETIME NOT NULL DEFAULT NOW(),
    TO_DELIVER BOOLEAN NOT NULL DEFAULT 0,
    CUSTOMER_ID INT(9) NOT NULL,
    PRODUCT_ID INT(9) NOT NULL,
    PRIMARY KEY (LINE_ID, INVOICE_ID) 
);

What is the correct way to set a default value for a datetime field?

Answer 1

if you have version 5.6.5, then it is possible to set a default value on a datetime column

you can do like that

  INVOICE_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Answer 2

A BEFORE INSERT trigger can be used to assign values to columns, including assigning the current date and time to a DATETIME column.

DELIMITER $$
CREATE TRIGGER invoice_bi 
BEFORE INSERT ON invoice
FOR EACH ROW
BEGIN
   IF NEW.invoice_date IS NULL THEN
      NEW.invoice_date = NOW();
   END IF;
END;
$$
DELIMITER ;

Prior to MySQL 5.6.x, the only default values that can be assigned to DATETIME columns were literals. Only TIMESTAMP columns could be assigned a default value of current date and time. If you change the column to TIMESTAMP be aware of the "automatic" initialization AND update behavior. To have the column initialized and not have it updated whenever the row is changed...

invoice_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP