Add default value for date column

Question

I am trying to add an default value for my date column. The default value should be GETDATE(). I want to do this so I can see the date of the created row. When I try to save it I get the warning: Incorrect default value for date.

Does someone know why I am getting the warning and how I can fix it?

It looks like the possibilities for default values in MySQL are rather restricted compared to what's allowed in a normal insert/update, but does this work? (It's for another type, wondering if it works for date too) https://stackoverflow.com/a/10603198 — Score_Under, Jul 05 '17 at 01:32
How are you setting it? via an alter table statement show your code — Tik, Jul 05 '17 at 01:38

score 1 · Accepted Answer · answered Jul 05 '17 at 01:45

1

Run this query

ALTER TABLE `YourDb.yourtable`
    CHANGE COLUMN `date` `date` DATETIME DEFAULT CURRENT_TIMESTAMP;

The Change column is oldval newval and the type make sure if your column name isnt date to change it appropriately

additionally if you want that timestamp to update when the row is modified use

CHANGE COLUMN `date` `date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

answered Jul 05 '17 at 01:45

Tik

822
6
14

This method is working. But it is also adding the time. I just want to add date (year-month-day). How can I change it to (year-month-day) – John Jul 05 '17 at 01:50
1

mysql only alows default timestamps for for the timestamp and datetime date types. you can remove the time portion in select query by using the date() function e.g. SELECT DATE(date) FROM `YourDb.yourtable` – Tik Jul 05 '17 at 02:09

Add default value for date column

1 Answers1