[mysql] Error in MySQL when setting default value for DATE or DATETIME

I'm running MySql Server 5.7.11 and this sentence:

updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

is not working. Giving the error:

ERROR 1067 (42000): Invalid default value for 'updated'

But the following:

updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

just works.

The same case for DATE.

As a sidenote, it is mentioned in the MySQL docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

even if they also say:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

Having also into account the second quote from MySQL documentation, could anyone let me know why it is giving that error?

This question is related to mysql sql date datetime console

The answer is

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


However to allow the format 0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command

By command

SET sql_mode = '';


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.


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.


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

I had this error with WAMP 3.0.6 with MySql 5.7.14.


change line 70 (if your ini file is untouched) in c:\wamp\bin\mysql\mysql5.7.14\my.ini file from




and restart all services.

This will disable strict mode. As per the documentation, “strict mode” means a mode with either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled. The documentation says:


I got into a situation where the data was mixed between NULL and 0000-00-00 for a date field. But I did not know how to update the '0000-00-00' to NULL, because

 update my_table set my_date_field=NULL where my_date_field='0000-00-00'

is not allowed any more. My workaround was quite simple:

update my_table set my_date_field=NULL where my_date_field<'1000-01-01'

because all the incorrect my_date_field values (whether correct dates or not) were from before this date.

First select current session sql_mode:

SELECT @@SESSION.sql_mode;

Then you will get something like that default value:


and then set sql_mode without 'NO_ZERO_DATE':


If you have grants, you can do it also for GLOBAL:

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = '...';

Config syntax issue

On some versions of MYSQL (tested 5.7.*) under *nix systems you should use this syntax:



These won't work:

dash no quotes


underscore no quotes


underscore and quotes


A more complete review of config values and sql-mode:

How to setup permanent Sql Mode flags

Just add the line: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

inside file: /etc/mysql/mysql.conf.d/mysqld.cnf

then sudo service mysql restart

It works for 5.7.8:

mysql> create table t1(updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
| Table | Create Table                                                                                                            |
| t1    | CREATE TABLE `t1` (
  `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
1 row in set (0.00 sec)

mysql> select version();
| version() |
| 5.7.8-rc  |
1 row in set (0.00 sec)

You can create a SQLFiddle to recreate your issue.


If it works for MySQL 5.6 and 5.7.8, but fails on 5.7.11. Then it probably is a regression bug for 5.7.11.

To solve the problem with MySQL Workbench (After applying the solution on the server side) :

Remove SQL_MODE to TRADITIONAL in the preferences panel.

enter image description here

This answer it's just for MySQL 5.7:

Best is not really set in blank the sql_mode, instead use in PHP a session variable with:


So at least you keep the other default values.

It's crazy that mysql documentation is not clear, you need delete to these default values in sql_mode:

NO_ZERO_IN_DATE,NO_ZERO_DATE, I understand, but in the future versions this will be discontinued.

STRICT_ALL_TABLES, with this, before parameters will be ignored, so you need to delete it too.

Finally TRADITIONAL too, but documentation speaks about this parameter: “give an error instead of a warning” when inserting an incorrect value into a column", with this parameter, dates with zero values is not inserted, but without yes.

MySQL is not really organised with these parameters and combinations.

I've tested a fix as follow:

1). On the file "system/library/db/mysqli.php" search and comment the line: 
"$this->connection->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION'");"

2) Add the following line above the one you just commented:
// Correction by Added by A.benkorich
$this->connection->query("SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'");

Option combinations for mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64).

Doesn't throw:




My settings in /etc/mysql/my.cnf on Ubuntu:


In diretory xamp/mysql/bin Open "my.ini" and change line: Sql_node for ->




Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with sql tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with date tag:

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer? Moment.js - How to convert date string into date? Extract Month and Year From Date in R #1292 - Incorrect date value: '0000-00-00' Extract year from date Error in MySQL when setting default value for DATE or DATETIME How to Extract Year from DATE in POSTGRESQL Format date as dd/MM/yyyy using pipes Moment.js - tomorrow, today and yesterday Can I use an HTML input type "date" to collect only a year? Formatting a Date String in React Native How to initialize a variable of date type in java? How to properly add 1 month from now to current date in moment.js How to convert dd/mm/yyyy string into JavaScript Date object? Get only records created today in laravel Add A Year To Today's Date LocalDate to java.util.Date and vice versa simplest conversion? PHP date time greater than today Convert String to Carbon Moment Js UTC to Local Time HTML Display Current date Hive cast string to date dd-MM-yyyy moment.js, how to get day of week number How to convert an Instant to a date format? Oracle SQL - DATE greater than statement Python - Get Yesterday's date as a string in YYYY-MM-DD format Get date from input form within PHP How to force the input date format to dd/mm/yyyy? Format date and Subtract days using Moment.js How to add minutes to current time in swift How to convert Moment.js date to users local timezone? How to compare LocalDate instances Java 8 Getting Current date, time , day in laravel How can I parse / create a date time stamp formatted with fractional seconds UTC timezone (ISO 8601, RFC 3339) in Swift? Moment.js get day name from date Get current date in DD-Mon-YYY format in JavaScript/Jquery Getting the difference between two Dates (months/days/hours/minutes/seconds) in Swift How to convert a date to milliseconds Using momentjs to convert date to epoch then back to date Parsing date string in Go Java 8: Difference between two LocalDateTime in multiple units

Questions with datetime tag:

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")? How do I filter date range in DataTables? Convert a string to datetime in PowerShell How to insert current datetime in postgresql insert query How to change the datetime format in pandas Pandas: Subtracting two date columns and the result being an integer finding first day of the month in python Error in MySQL when setting default value for DATE or DATETIME How to Extract Year from DATE in POSTGRESQL java.time.format.DateTimeParseException: Text could not be parsed at index 21 How can I create a Java 8 LocalDate from a long Epoch time in Milliseconds? Is there a simple way to increment a datetime object one month in Python? Pandas: Convert Timestamp to datetime.date How to insert date values into table In Oracle SQL: How do you insert the current date + time into a table? Getting today's date in YYYY-MM-DD in Python? What's the difference between Instant and LocalDateTime? How to convert string to datetime format in pandas python? What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types Format datetime to YYYY-MM-DD HH:mm:ss in moment.js How to convert an Instant to a date format? Python - Get Yesterday's date as a string in YYYY-MM-DD format python pandas extract year from datetime: df['year'] = df['date'].year is not working Create a day-of-week column in a Pandas dataframe using Python datetime to string with series in python pandas How to get current time in python and break up into year, month, day, hour, minute? How to force the input date format to dd/mm/yyyy? What exactly does the T and Z mean in timestamp? How to add/subtract time (hours, minutes, etc.) from a Pandas DataFrame.Index whos objects are of type datetime.time? Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query How do I get the current timezone name in Postgres 9.3? Datetime current year and month in Python How to convert datetime to integer in python Convert Pandas Series to DateTime in a DataFrame Extract time from moment js object Get current date in DD-Mon-YYY format in JavaScript/Jquery Unable to obtain LocalDateTime from TemporalAccessor when parsing LocalDateTime (Java 8) How do I properly set the Datetimeindex for a Pandas datetime object in a dataframe? Calculate time difference in minutes in SQL Server Convert Pandas Column to DateTime Inserting created_at data with Laravel

Questions with console tag:

Error in MySQL when setting default value for DATE or DATETIME Where can I read the Console output in Visual Studio 2015 Chrome - ERR_CACHE_MISS Swift: print() vs println() vs NSLog() Datatables: Cannot read property 'mData' of undefined How do I write to the console from a Laravel Controller? Cannot read property 'push' of undefined when combining arrays Very simple log4j2 XML configuration file using Console and File appender Console.log not working at all Chrome: console.log, console.debug are not working Send mail via CMD console Java using scanner enter key pressed Getting Keyboard Input %i or %d to print integer in C using printf()? Use Device Login on Smart TV / Console When tracing out variables in the console, How to create a new line? How can I write these variables into one line of code in C#? how to print a string to console in c++ Start/Stop and Restart Jenkins service on Windows How to make Unicode charset in cmd.exe by default? How to run Ruby code from terminal? What's the difference between console.dir and console.log? How to get Rails.logger printing to the console/stdout when running rspec? How to open Console window in Eclipse? How to color the Git console? How to Clear Console in Java? How can I get the console logs from the iOS Simulator? App.Config file in console application C# How to change node.js's console font color? How to use Console.WriteLine in ASP.NET (C#) during debug? Printing string variable in Java Masking password input from the console : Java Reading value from console, interactively jQuery: Clearing Form Inputs Disable Rails SQL logging in console Save the console.log in Chrome to a file Colors in JavaScript console How can I read user input from the console? How to save all console output to file in R? Difference between Console.Read() and Console.ReadLine()? Basic Apache commands for a local Windows machine Read input from console in Ruby? Node.js: printing to console without a trailing newline? Command to close an application of console? How to include JavaScript file or library in Chrome console? SyntaxError: Unexpected Identifier in Chrome's Javascript console How do I launch the Android emulator from the command line? How to write to Console.Out during execution of an MSTest test How can I align text in columns using Console.WriteLine? vbscript output to console