Avatar of William Nettmann
William Nettmann
Flag 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?
LinuxMySQL ServerSQL

Avatar of undefined
Last Comment
William Nettmann

8/22/2022 - Mon
Manuel Marienne-Duchêne

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
William Nettmann

ASKER
I found my own answer.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck