Menu
In an existing database, I've age column (INT). Now I need to set it as dob (DATETIME).
I try doing so through PHPMyAdmin, giving CURRENT_TIMESTAMP as default value as defined by answer with 138 upvotes. However PHPMyAdmin is complaining #1067 - invalid default value for 'dob' as in attached screenshot:
Can someone please suggest why I'm getting that error and how to fix that?
229
DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0.
You can't set CURRENT_TIMESTAMP as default value with DATETIME. But you can do it with TIMESTAMP. See the difference here. The DEFAULT value clause in a data type specification indicates a default value for a column.
Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.
You can't set CURRENT_TIMESTAMP as default value with DATETIME.
But you can do it with TIMESTAMP.
See the difference here.
Words from this blog
The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.
This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.
The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
100
Set the type of the field as TIMESTAMP too.
4
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
