Link to home
Start Free TrialLog in
Avatar of William Nettmann
William NettmannFlag for South Africa

asked on

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?
Avatar of Manuel Marienne-Duchêne
Manuel Marienne-Duchêne
Flag of France image

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
Avatar of William Nettmann

ASKER

Thanks, Manumd - that is obviously why I have a problem.

Now, I need to find a way to fix it.
ASKER CERTIFIED SOLUTION
Avatar of William Nettmann
William Nettmann
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found my own answer.