Solved

Problem with DATETIME column in MySQL

Posted on 2016-07-30
4
50 Views
Last Modified: 2016-08-04
I have a table with a column defined as follows:
`Listing_Date` datetime DEFAULT NULL,

Open in new window

Running the following queries return results normally:
select count(*) from caselaw.cases;
select count(*) from caselaw.cases where Listing_Date = '0000-00-00 00:00:00';

Open in new window

However,
update caselaw.cases set Listing_Date = null where Listing_Date = '0000-00-00 00:00:00';

Open in new window

returns the following error:
Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'Listing_Date' at row 1
How can I get rid of the '0000-00-00 00:00:00' values?
0
Comment
Question by:William Nettmann
  • 3
4 Comments
 
LVL 6

Expert Comment

by:Manuel Marienne-Duchêne
ID: 41735751
In myssql doc the limit date is

'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

http://dev.mysql.com/doc/refman/5.7/en/datetime.html
0
 
LVL 12

Author Comment

by:William Nettmann
ID: 41735756
Thanks, Manumd - that is obviously why I have a problem.

Now, I need to find a way to fix it.
0
 
LVL 12

Accepted Solution

by:
William Nettmann earned 0 total points
ID: 41735773
Found a workaround:

Add
[mysqld]
sql_mode = 

Open in new window

tp the MySQL configuration file, which disables "strict mode", and the update can be done.
0
 
LVL 12

Author Closing Comment

by:William Nettmann
ID: 41742159
I found my own answer.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question