mysql - Need to allow '0000-00-00 00:00:00' dates

Question

I want zero dates to be allowed in MySQL. I have changed the sql_mode to ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION.

I have changed it in /etc/mysql/my.cnf.

Yet, I when I try to insert data I get the error,

Data truncation: Incorrect datetime value: '0000-00-00 00:00:00'

The MySQL version is 5.7.18.

Any ideas on this would be of great help.

What data were you inserting when this error happened? Why do you need to allow invalid dates? — Tim Biegeleisen, Apr 24 '17 at 13:13
Well, I think in `varchar` field. So, why you don't use `varchar`? when you execute select, update, delete, ... you can convert with `DATE()` like `SELECT id FROM my_table where DATE(my_date_1) > DATE(my_date_2) ORDER BY DATE(my_date_3) DESC`... And... you can check if is a zero date like `WHERE my_date_1 = '0000-00-00 00:00:00'` — Olaf Erlandsen, Apr 24 '17 at 13:16
I get the data from SQL server and I pass it to mysql. The incoming date looks this way : '0000-00-00 00:00:00'. This is just a back up system, and I need to keep the data as it is in the source system — Rathi Rao, Apr 24 '17 at 13:16
I cannot store the date in varchar as I cannot change the data type and I am not allowed to do it. And yeah, I did restart mysql after the changes — Rathi Rao, Apr 24 '17 at 13:20
Possible duplicate of [Error in mysql when setting default value for DATE or DATETIME](https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime) — Ravindra S, Dec 07 '17 at 14:37

score 12 · Answer 1 · edited May 05 '22 at 20:56

To allow zero dates (0000-00-00 00:00:00), you need to remove that restriction.

To remove particular SQL mode (in this case NO_ZERO_DATE), find the current SQL mode:

SELECT @@GLOBAL.sql_mode;

copy the result and remove from it what you don't need (NO_ZERO_DATE)

e.g.:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

create and open this file:

sudo vim /etc/mysql/conf.d/disable_strict_mode.cnf

and write and past into it your new SQL mode:

[mysqld]
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restart MySQL:

sudo service mysql restart

score 2 · Answer 2 · answered Apr 24 '17 at 16:01

I am going to assume here that you want to have a valid date so that your queries never have to check for NULL.

One way to do this, is to use what I like to call "In perpetuity" date(s).

These are essentially the min/max dates allowable for the DATETIME data type.

In my uses, there were typically "windows" of from - to pairs, but you might only need the minimum date.

From the Mysql manual:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

So one way that might work for you, is to utilize '1000-01-01 00:00:00' instead of the zero date.

score 1 · Answer 3 · answered Apr 24 '17 at 14:55

1

You might want to read about other mode settings, such as strict and NO_ZERO_IN_DATE and NO_ZERO_DATE https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

answered Apr 24 '17 at 14:55

Peter Gulutzan

465
2
8

score 0 · Answer 4 · answered Apr 12 '18 at 08:05

0

I could solve this by using zeroDateTimeBehavior=convertToNull

answered Apr 12 '18 at 08:05

Rathi Rao

287
1
4
19

score 0 · Answer 5 · edited Mar 27 '20 at 21:37

0

My solution when update table

SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0

edited Mar 27 '20 at 21:37

stealthyninja

10,343
11
51
59

answered Mar 27 '20 at 15:30

VietPublic

39
2

score -1 · Answer 6 · answered Apr 24 '17 at 13:25

It should go without saying that you're doing it wrong. The column should be nullable if you intend to store nothing, and you shouldn't try to insert an empty string when you should be using a NULL.

Consider this, though:

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings. But when strict mode is in effect...

For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict SELECT @@SQL_MODE; should reveal that you are running with (at least) STRICT_TRANS_TABLES. While this isn't the internal default in 5.6, it is included in the default 5.6 configuration file. You'll want to remove it and restart the server if you want the old behavior.

...although you should consider making your code behave more correctly.

Try enabling this

ALLOW_INVALID_DATES

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_allow_invalid_dates

Note: I do not recommend doing this. I'm just answering how to do it. In my opinion, it is better to clean up the queries and data, and provide a good default for columns that are non-null.

The OP has already enabled ALLOW_INVALID_DATES. That's exactly the question: ALLOW_INVALID_DATES is enabled, yet MySQL provides an error when a zero date is used. — Shadow, Apr 24 '17 at 13:39

mysql - Need to allow '0000-00-00 00:00:00' dates

6 Answers6