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

From: bill <william_at_TechServSys.com>
Date: Sun, 4 Feb 2018 14:56:37 -0500
Message-ID: <p57ohm$14pe$1_at_gioia.aioe.org>

On 2/4/2018 12:01 PM, The Natural Philosopher wrote:

> On 04/02/18 16:13, bill 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. 
> 
> "I can SELECT * FROM `episodes` where `ending` = '0000-00-00' 
> without error" seems to give the lie to that assertion
> 

correct
now try `ending` = '0000-00-00' in the where clause

> 
>   If I can't read it in the where clause, I
>> can't modify it with an update command.
>> -bill
>>
> 
> 

Received on Sun Feb 04 2018 - 20:56:37 CET