-1

Can't understand why I get Error Code: 1525. Incorrect DATETIME value: '0000-00-00 00:00:00'

(pls note that it's not a duplicate, since there was no direct answer to the problem)

I have an old db table with data that I want to import using SQL.
In the old table there are columns with '0000-00-00 00:00:00' values. I know about zero-value-dates and >mysql 5.7. and it's ok, whenever I have it, I will to use current_timestamp().
In order to do so, I need to check the data first.. So I used this SQL:

INSERT INTO new_table
(
  column_a,
  date_column
)
SELECT 
 column_a,
 IF(
     strcmp(`date_column`,'0000-00-00 00:00:00')=0, 
     current_timestamp(), 
     `date_column`
 )         
FROM 
   old_table

But it's not working, as mentioned.

yossi
  • 3,090
  • 7
  • 45
  • 65

1 Answers1

0

Try this:

INSERT INTO new_table
(
  column_a,
  date_column
)
SELECT 
 column_a,
 ifnull(nullif(`date_column`,
               '0000-00-00 00:00:00'),      
        current_timestamp())      
FROM 
   old_table
Alessandro
  • 129
  • 1
  • What datatypes are the columns "date_column" both in the "old_table" and "new_table"? – Alessandro Apr 27 '23 at 09:50
  • @yossi How can this answer be accepted when it triggers the same error as your original query? - [db<>fiddle](https://dbfiddle.uk/hqH2r8xs) – user1191247 Apr 27 '23 at 09:54
  • I don't know! in any case I don't understand if the problem occurs selecting from an old database or inserting an old value into a new structure. In addition pay attention at the datatype. When we speak about date the type timestamp and datetime are very similar but they react in a different way in some particular conditions. – Alessandro Apr 27 '23 at 10:00
  • just to undestand, try to insert '0000-00-00 00:00:00' or '1970-01-01 00:00:00' into a datetime and timestamp datetypes field and check the differences! – Alessandro Apr 27 '23 at 10:17
  • what datatype are you using for date_column? can you provide the structure of the tables? – Alessandro Apr 27 '23 at 10:19
  • I don't know! I only answer to your question. In any case I have tested the code on my databases and it works properly both in 5.6.14 and 10.3.16-mariaDB version. if you provide the requasted additional info I can help you to solve the problem definitely! – Alessandro Apr 27 '23 at 10:27