LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   mysql: ERROR 1292 (22007): Incorrect date value: '0000-00-00' (https://www.linuxquestions.org/questions/programming-9/mysql-error-1292-22007-incorrect-date-value-%270000-00-00%27-4175735856/)

MakeTopSite 04-10-2024 03:24 AM
mysql: ERROR 1292 (22007): Incorrect date value: '0000-00-00'
 
Code:
mysql> UPDATE some_table SET some_date_column='2024-04-10' WHERE some_date_column='0000-00-00' ;
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'some_date_column' at row 1
Server version: 8.0.26 MySQL Community Server - GPL

What is please optimal solution ?

pan64 04-10-2024 04:41 AM
without details hard to say anything. What is that database, what is that some_table and what is that some_date_column? Anyway, 0000-00-00 is not a valid date. It also may depend on the configuration of the database/table/whatever.
So how do you think we could tell what the optimal solution would be? Probably: some_date_column='1732-11-23'

NevemTeve 04-10-2024 05:42 AM
Code:
UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column<='0001-01-01';
Or maybe you are referring to NULL-value?
Code:
UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column IS NULL;

Guttorm 04-10-2024 05:48 AM
Hi

It has to do with sql mode:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

The default has changed in newer versions. After 8.0, the default setting for allow invalid dates changed:

https://dev.mysql.com/doc/refman/8.3...sql-modes.html

The setting you need to change is probably NO_ZERO_DATE.

sundialsvcs 04-23-2024 07:37 AM
... but you should also fix that database! Maybe you temporarily set the configuration to allow "0000-00-00" to be referenced, having determined that the value is actually there. Then, one time, execute a query to UPDATE those values to NULL as they should be. Then, promptly set the configuration back.

(Of course, you will also need to first review the source-code of any applications which now reference that database, so that your change does not break them.)

MakeTopSite 05-04-2024 02:29 AM
Quote:
Originally Posted by pan64 (Post 6495121)
without details hard to say anything. What is that database, what is that some_table and what is that some_date_column? Anyway, 0000-00-00 is not a valid date. It also may depend on the configuration of the database/table/whatever.
So how do you think we could tell what the optimal solution would be? Probably: some_date_column='1732-11-23'
I'm sorry I don't know which info is useful for you and which is not. I don't want break privacy by sending useless information.

generouspaper 05-04-2024 02:38 AM
There are some valid points here. Although, in a production environment you would never have the need to update a null datetime. Instead do some debugging and find out why it inserted with date 0000-00-00 in the first place.

pan64 05-04-2024 10:03 AM
Quote:
Originally Posted by MakeTopSite (Post 6499769)
I'm sorry I don't know which info is useful for you and which is not. I don't want break privacy by sending useless information.
It is definitely wrong approach. I don't need any information. You need to provide everything you think related and does not brake privacy or any other rules. You also hide sensitive information if you wish.
Sending useless information is not a problem, but hiding relevant information can make it intractable.
Remember, we don't see your host, we don't know your thoughts.
http://catb.org/~esr/faqs/smart-ques...html#beprecise

ntubski 05-04-2024 03:15 PM
Isn't this already solved by Guttorm's post #4?

MakeTopSite 05-11-2024 02:58 AM
Quote:
Originally Posted by NevemTeve (Post 6495134)
Code:
UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column<='0001-01-01';
Or maybe you are referring to NULL-value?
Code:
UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column IS NULL;
Thank you this has solved the problem.

MakeTopSite 05-11-2024 02:58 AM
Quote:
Originally Posted by ntubski (Post 6499864)
Isn't this already solved by Guttorm's post #4?
yes it is

MakeTopSite 05-11-2024 03:03 AM
Quote:
Originally Posted by pan64 (Post 6495121)
without details hard to say anything. What is that database, what is that some_table and what is that some_date_column? Anyway, 0000-00-00 is not a valid date. It also may depend on the configuration of the database/table/whatever.
So how do you think we could tell what the optimal solution would be? Probably: some_date_column='1732-11-23'
All necessary information has been already posted in the first post.
Do you think
Code:
some_date_column='1732-11-23'
will work ?

MakeTopSite 05-11-2024 03:07 AM
Quote:
Originally Posted by sundialsvcs (Post 6497876)
... but you should also fix that database! Maybe you temporarily set the configuration to allow "0000-00-00" to be referenced, having determined that the value is actually there. Then, one time, execute a query to UPDATE those values to NULL as they should be. Then, promptly set the configuration back.

(Of course, you will also need to first review the source-code of any applications which now reference that database, so that your change does not break them.)
Yes I'll fix it.

MakeTopSite 05-11-2024 03:10 AM
Quote:
Originally Posted by generouspaper (Post 6499770)
There are some valid points here. Although, in a production environment you would never have the need to update a null datetime. Instead do some debugging and find out why it inserted with date 0000-00-00 in the first place.
It was probably caused by inserting undefined date value (non production environment). It's already fixed.


All times are GMT -5. The time now is 12:11 PM.