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

From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Sun, 4 Feb 2018 14:03:10 +0000
Message-ID: <p573qv$rd1$1_at_dont-email.me>


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

-- 
Microsoft : the best reason to go to Linux that ever existed.
Received on Sun Feb 04 2018 - 15:03:10 CET

Original text of this message