MySQL Syntax for setting Default Date

Question

How can we set element to some default date in mysql ?

• In oracle we would do something like

  start_date DATE DEFAULT to__date('01011900','DDMMYYYY')

  mutation_date_time DATE DEFAULT SYSDATE

  CONSTRAINT entity_specification UNIQUE(external_name, start_date_time, end_date_time))

Also do we have any site or resource where we can get MySQL equivalent syntax to Oracle Syntax.

Update

I tried to do as mentioned in answer but again am getting error message:

    create table product_offer_type(object_id INT(19), snapshot_id INT(19), PRIMARY KEY(object_id,snapshot_id), enum_value VARCHAR(64) NOT NULL, external_name VARCHAR(64) NOT NULL, description VARCHAR(255), business_validation INT(1), valid_for_start_date_time DATE DEFAULT '1900-01-10', valid_for_end_date_timeDATE DEFAULT '4712-01-01', mutation_date_time DATE DEFAULT 'SYSDATE()', mutation_user VARCHAR(32) DEFAULT 'USER');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT '4712-01-01', mutation_date_time DATE DEFAULT 'SYSDATE()', mutation_user' at line 1
mysql>

Answer 1

Simply pass it a formatted string.

start_date DATE DEFAULT '1900-01-01'

Answer 2

For a fixed default date on a DATE field, the format is 'YYYY-MM-DD', like start_date DATE DEFAULT '2009-10-30'. DATETIME would be 'YYYY-MM-DD HH:MM:SS'.

For a dynamic date based on the current date, you can use DEFAULT CURRENT_TIMESTAMP if you are using a TIMESTAMP field; otherwise, it is not possible to use a function as a DEFAULT in MySQL. TIMESTAMP fields are formatted as 'YYYY-MM-DD HH:MM:SS'. Otherwise, you'd have to get the current date in your code before inserting the data.