How can I make a column's default value equal to the current date + 30 days in MySQL? For example, if current date is 10-1-2011 then the column value must be inserted as 9-2-2011.
Insert a date that updates automaticallyOn the Insert tab, in the Text group, click Date & Time. In the Date and time dialog box, select the format you want. Select the Update automatically check box. The date is inserted as a field and will update automatically.
CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL ); DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0.
If you're using MySQL >= 5.0, use a trigger:
CREATE TRIGGER setDefaultDate
BEFORE INSERT ON tableName
FOR EACH ROW
SET NEW.date = ADDDATE(curdate(), INTERVAL 30 DAY);
The trigger will activate when you insert into tableName
, setting date
to now + 30 days. If your insert sets the date, it will override this default due to the BEFORE
. The date is calculated using ADDDATE
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With