mysql: unable to modify or update column for date value 0000-00-00

user_4035095 0
Viewed 4110

I have two questions:

  1. Why does mysql is not able to notify error when inserting wrong date 0000-00-00 when in the table definition its set to not null..?

example Schema: here

Now if change the duedate to 1970-01-01 it shows error

Incorrect date value 0000-00-00 for the column duedate at row1

update my_list set duedate = '1970-01-01' where duedate = '0000-00-00';

I tried to modify the column but it still shows the same error

ALTER TABLE `my_list` modify `duedate` date NULL;
ALTER TABLE  `my_list` MODIFY  `duedate` DATE NOT NULL DEFAULT '1970-01-01';
  1. How do I change the duedate to 1970-01-01 for 0000-00-00;

I got the database from the client so unfortunately work on my.cnf file.

More info:

MYSQL version: 5.5

EDIT: This is working 

update my_list set duedate = '1970-01-01' where id = 16;

but not 

update my_list set duedate = '1970-01-01' where duedate = '0000-00-00';
mysql
3 Answers

Cast duedate to string:

update my_list set duedate = '1970-01-01' where DATE_FORMAT(duedate, '%Y-%m-%d') = '0000-00-00';
user_102851470

You are not enable modify and update date value to '0000-00-00' as a dummy date. because of MySQL permits you to store a “zero” value. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.

Additionally strict mode has to be enabled for disallowing "zero" values:

Check version and SQL mode

SELECT version();
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Disabling STRICT_TRANS_TABLES mode

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 = '';

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. For windows this has to be done in my.ini file.

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. For windows this has to be done in my.ini file.

Note

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 NO_ZERO_DATE

As of MySQL as of 5.7.4 this mode is deprecated.Refer MySQL 5.7 documentation on NO_ZERO_DATE

user_51805270

This works

update my_list
set duedate = '1970-01-01' 
where to_days(duedate)IS NULL

at least in sqlfiddle

user_24860020
Related