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

From: bill <william_at_TechServSys.com>
Date: Sun, 4 Feb 2018 11:13:48 -0500
Message-ID: <p57bft$ean$1_at_gioia.aioe.org>


[Quoted] 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 Received on Sun Feb 04 2018 - 17:13:48 CET

Original text of this message