Re: '0000-00-00' is an invalid date
From: bill <william_at_TechServSys.com>
Date: Mon, 5 Feb 2018 10:25:17 -0500
Message-ID: <p59t0t$gfq$1_at_gioia.aioe.org>
>>>> 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
Date: Mon, 5 Feb 2018 10:25:17 -0500
Message-ID: <p59t0t$gfq$1_at_gioia.aioe.org>
On 2/5/2018 8:09 AM, Richard Yates wrote:
> 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. >
That is a good idea, thanks
-bill
Received on Mon Feb 05 2018 - 16:25:17 CET