Re: '0000-00-00' is an invalid date

On Sun, 4 Feb 2018 11:13:48 -0500, bill <william_at_TechServSys.com> wrote:

>On 2/4/2018 9:03 AM, The Natural Philosopher wrote:
>> On 04/02/18 12:55, bill wrote:
>>> I have a database with a a field 'episodes.ending'.  For
>>> reasons that are lost in time if the episode has not ended the
>>> ending field contains '0000-00-00' rather than NULL.
>>>
>>> In upgrading to 16.04 LTS from 14.4 LTS I am now advised that
>>> "1292 - Incorrect datetime value: '0000-00-00' for column
>>> 'ending' at row 1".
>>>
>>> looking up the error -> Error: 1292 SQLSTATE: 22007
>>> (ER_TRUNCATED_WRONG_VALUE)
>>> Message: Truncated incorrect %s value: '%s'
>>>
>>> Lest any of you worry about my mental state, I am aware that
>>> '0-000-00-00' is not a valid datetime.
>>>
>>> I can SELECT * FROM `episodes` where `ending` = '0000-00-00'
>>> without error, but
>>> update`episodes`  set `ending` = NULL
>>> where `ending` = '0000-00-00'
>>> gives me the error.
>>>
>>> any suggestions how to render the 'ending' field updatable?
>>>
>>> -bill
>>
>> alter the table structuire so that NULL is an allowable option
>> for that field?
>>
>> https://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql
>
> Null is allowable and the default.
>The problem seems to be that MySQL will not allow reading the
>value '0000-00-00' in date field. If I can't read it in the
>where clause, I can't modify it with an update command.
>-bill

I ran into something similar when there was an upgrade of phpmyadmin.

Try changing the data type to text, replacing the 0000-00-00 with NULL and changing back to date data type. Received on Mon Feb 05 2018 - 14:09:36 CET