Re: '0000-00-00' is an invalid date
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Sun, 4 Feb 2018 17:01:55 +0000
Message-ID: <p57ea3$3qd$2_at_dont-email.me>
>>> 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: Sun, 4 Feb 2018 17:01:55 +0000
Message-ID: <p57ea3$3qd$2_at_dont-email.me>
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
If I can't read it in the where clause, I
> can't modify it with an update command. > -bill >
-- It’s easier to fool people than to convince them that they have been fooled. Mark TwainReceived on Sun Feb 04 2018 - 18:01:55 CET