Menu
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.
898
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.
142
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
