Error in MySQL when setting default value for DATE or DATETIME
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
MySQL Documentation 5.7 says:
Strict mode affects whether the server permits '0000-00-00' as a valid date:
If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.
If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.
To Check MYSQL mode
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
Disabling STRICT_TRANS_TABLES mode
However to allow the format 0000-00-00 00:00:00
you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command
By command
SET sql_mode = '';
or
SET GLOBAL sql_mode = '';
Using the keyword GLOBAL
requires super previliges and it affects the operations all clients connect from that time on
if above is not working than go to /etc/mysql/my.cnf
(as per ubuntu) and comment out STRICT_TRANS_TABLES
Also, if you want to permanently set the sql mode at server startup then include SET sql_mode=''
in my.cnf
on Linux or MacOS. For windows this has to be done in my.ini
file.
Note
However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 6 documentation which says
MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.
UPDATE
Regarding the bug matter as said by @Dylan-Su:
I don't think this is the bug it the way MYSQL is evolved over the time due to which some things are changed based on further improvement of the product.
However I have another related bug report regarding the NOW()
function
Datetime field does not accept default NOW()
Another Useful note [see Automatic Initialization and Updating for TIMESTAMP and DATETIME]
As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.
Update Regarding NO_ZERO_DATE
As of MySQL as of 5.7.4 this mode is deprecated. For previous version you must comment out the respective line in the config file. Refer MySQL 5.7 documentation on NO_ZERO_DATE
MySQL Incorrect datetime value: '0000-00-00 00:00:00'
My suggestion if it is the case that the table is empty or not very very big is to export the create statements as a .sql file, rewrite them as you wish. Also do the same if you have any existing data, i.e. export insert statements (I recommend doing this in a separate file as the create statements). Finally, drop the table and execute first create statement and then inserts.
You can use for that either mysqldump
command, included in your MySQL installation or you can also install MySQL Workbench, which is a free graphical tool that includes also this option in a very customisable way without having to look for specific command options.
Error in setting the default value to CURRENT_TIMESTAMP
Prior to MySQL 5.6.5, you can only use the CURRENT_TIMESTAMP
default value for columns of type TIMESTAMP
.
See https://stackoverflow.com/a/9005872/1293303
Mysql8.0.22 set default value DATE_FORMAT(sysdate() ,'%Y%m%d') error
Read https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html
In particular:
The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
So in MySQL, unlike MariaDB, you need to put an expression inside parentheses when using it as a DEFAULT.
Example:
create table if not exists `test` (
`client_id` varchar(18) not null default ' ',
`begin_date` int not null default (DATE_FORMAT(sysdate() ,'%Y%m%d')),
`end_date` int not null default (DATE_FORMAT(sysdate() ,'%Y%m%d')),
unique index `uk_key` (`client_id` asc)
) engine = InnoDB default charset = utf8 collate = utf8_bin comment = '';
mysql DATE datatype's default value not working
You problem is that you are not using a valid date.
The minimum valid date in MySQL is '1000-01-01'
, each RDBMS usually have its own minimum date value. Oracle is 0001-01-01
and SQL-Server is 01/01/1753
.
EDIT: By the error you are receiving , I believe you also have time value in this column? Try changing it to datetime
ALTER TABLE employees MODIFY leave_date DATETIME NOT NULL DEFAULT '1000-10-01'
Or update the table and trunc the time value yourself, and then use your alter command.
Default Date Value in MySQL create table not working
Try this
CREATE TABLE bugs (id INT, open_date DATE,
close_date DATE DEFAULT '9999-99-99', severity INT);
for more information, use the below link:-
How do you set a default value for a MySQL Datetime column?
MySQL 5.7: Invalid default value for 'event_end_date'
The error happens already in your query on the first line. There you are trying to change the column event_start_date
, the error message however is for column event_end_date
. You need to change both columns with a single query in order to avoid this error:
ALTER TABLE events CHANGE event_start_date event_start_date date DEFAULT NULL, CHANGE event_end_date event_end_date date DEFAULT NULL;
It probably worked with your other table because you only had one column of type date
.
#1292 - Incorrect date value: '0000-00-00'
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation.
For more information read this.
Hope it helps.
How can I set the default value of a field as '0000-00-00 00:00:00'?
Cause of the error: the SQL mode
You can set the default value of a DATE
, DATETIME
or TIMESTAMP
field to the special "zero" value of '0000-00-00' as dummy date if the sql mode permits it. For MySQL versions lower than 5.7.4 this is ruled by the NO_ZERO_DATE mode, see this excerpt of the documentation:
MySQL permits you to store a “zero” value of '0000-00-00' as a
“dummy date.” This is in some cases more convenient than using NULL
values, and uses less data and index space. To disallow '0000-00-00',
enable the NO_ZERO_DATE SQL mode.
Additionally strict mode has to be enabled for disallowing "zero" values:
If this mode and strict mode are enabled, '0000-00-00' is not permitted
and inserts produce an error, unless IGNORE is given as well.
As of MySQL 5.7.4 this depends only on the strict mode:
Strict mode affects whether the server permits '0000-00-00' as a
valid date:If strict mode is not enabled, '0000-00-00' is permitted and inserts
produce no warning.If strict mode is enabled, '0000-00-00' is not permitted and inserts
produce an error, unless IGNORE is given as well. For INSERT IGNORE
and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a
warning.
Check version and SQL mode
So you should to check your MySQL version and the SQL mode of your MySQL server with
SELECT version();
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
Enable the INSERT
You can set the sql_mode for your session with SET sql_mode = '<desired mode>'
SET sql_mode = 'STRICT_TRANS_TABLES';
Valid range for DATETIME
The supported range for DATETIME
is
[1000-01-01 00:00:00] to ['9999-12-31 23:59:59'],
so the minimal valid DATETIME value is '1000-01-01 00:00:00'.
I wouldn't recommend to use this value though.
Additional Note
Since MySQL 5.6.5 all TIMESTAMP
and DATETIME
columns can have the magic behavior (initializing and/or updating), not only TIMESTAMP
and only one column at most, see Automatic Initialization and Updating for TIMESTAMP and DATETIME:
As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
initializated and updated to the current date and time (that is, the
current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
for at most one TIMESTAMP column per table. The following notes first
describe automatic initialization and updating for MySQL 5.6.5 and up,
then the differences for versions preceding 5.6.5.
You could change your CREATE TABLE statement in the case of MySQL 5.6.5 or newer to:
CREATE TABLE IF NOT EXISTS `article` (
`article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`url` VARCHAR(255) NOT NULL,
`title` VARCHAR(255) NOT NULL,
`date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
`date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
`backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
`created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The permanent datetime when the article is created.',
`updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM
AUTO_INCREMENT = 66
COMMENT = 'Entity that holds the article with one-to-one properties.';
Related Topics
How to Cast the Datetime to Time
Is There a Combination of "Like" and "In" in Sql
How to Make SQL Case Sensitive String Comparison on MySQL
Will Ansi Join Vs. Non-Ansi Join Queries Perform Differently
Conversion Failed When Converting Date And/Or Time from Character String While Inserting Datetime
How Rownum Works in Pagination Query
How to Get Multiple Counts With One SQL Query
How to Import an SQL File Using the Command Line in MySQL
Should I Use != or ≪≫ for Not Equal in T-Sql
How to Reset a Sequence in Oracle
Difference Between Scope_Identity(), Identity(), @@Identity, and Ident_Current()
How to Schedule a Job to Run a SQL Query Daily
Any Downsides of Using Data Type "Text" For Storing Strings
Optimal Way to Concatenate/Aggregate Strings
How to See What Character Set a MySQL Database/Table/Column Is
SQL Split Values to Multiple Rows