• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Problem with DATETIME column in MySQL

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
William Nettmann
Asked:
William Nettmann
  • 3
1 Solution
 
Manuel Marienne-DuchêneITMCommented:
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
 
William NettmannPHP Web DeveloperAuthor Commented:
Thanks, Manumd - that is obviously why I have a problem.

Now, I need to find a way to fix it.
0
 
William NettmannPHP Web DeveloperAuthor Commented:
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
 
William NettmannPHP Web DeveloperAuthor Commented:
I found my own answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now