0

When I run

UPDATE `my_table` SET `my_date` = NULL WHERE `my_date` = '0000-00-00'

it just says

java.sql.SQLDataException: (conn=9310) Incorrect date value: '0000-00-00' for column 'my_date' at row 1

John
  • 467
  • 1
  • 6
  • 23
  • Is the column nullable? i.e. defined as being not null in the create statement? – FreudianSlip Apr 26 '18 at 07:27
  • @FreudianSlip yes, it is nullable. – John Apr 26 '18 at 07:28
  • MariaDB [test]> select * from test; | id | testdate | | 1 | 2018-04-26 08:29:54 | | 2 | 0000-00-00 00:00:00 | | 3 | 0000-00-00 00:00:00 | 3 rows in set (0.00 sec) MariaDB [test]> update test set testdate=null where testdate='0000-00-00'; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 MariaDB [test]> select * from test; | id | testdate | | 1 | 2018-04-26 08:29:54 | | 2 | NULL | | 3 | NULL | Does it work if you do it in the CLI? – FreudianSlip Apr 26 '18 at 07:31
  • **ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'my_date' at row 1** @FreudianSlip Was able to select, but wasn't able to update. – John Apr 26 '18 at 07:43
  • Does this help? https://stackoverflow.com/questions/14625191/error-code-1292-incorrect-date-value-mysql – FreudianSlip Apr 26 '18 at 07:45
  • actually - i bet it's this : https://stackoverflow.com/questions/37292628/1292-incorrect-date-value-0000-00-00/37297536 – FreudianSlip Apr 26 '18 at 07:46
  • @FreudianSlip thanks. I got progress. – John Apr 26 '18 at 08:13
  • Dont forget to add your solution here - even if you answer your own question - it'll be useful for the next person. – FreudianSlip Apr 26 '18 at 08:14

0 Answers0