Remove all zero dates from MySQL database across all Tables

Question

I have plenty of tables in MySQL which which contains zero date in dateTime column 0000-00-00 00:00:00

Using some sort of admin settings, Is it possible to disable zero dates and replace all zero with static value say 1-1-1900?

EDIT:

I am working on database migration which involves migrating more than 100 MySQL tables to SQL Server.

Can I avoid executing scripts on each table manually by setting up database mode?

Answer 1

To change existings values you could use a query like this:

UPDATE tablename SET date_column = '1900-01-01' WHERE date_column = '0000-00-00';

If you want to automate the UPDATE query you can use a prepared statement:

SET @sql_update=CONCAT_WS(' ', 'UPDATE', CONCAT(_schema, '.', _table),
                               'SET', _column, '=', '\'1900-01-01\'',
                               'WHERE', _column, '=', '\'0000-00-00\'');

PREPARE stmt FROM @sql_update;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And you can loop through all colums in all tables on the current schema that are declared as date:

SELECT
  table_schema,
  table_name,
  column_name
FROM
  information_schema.columns
WHERE
  table_schema=DATABASE() AND data_type LIKE 'date%'

To loop through all columns you could use a stored procedure:

DELIMITER //
CREATE PROCEDURE update_all_tables() BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _schema VARCHAR(255);
  DECLARE _table VARCHAR(255);
  DECLARE _column VARCHAR(255);
  DECLARE cur CURSOR FOR SELECT
                           CONCAT('`', REPLACE(table_schema, '`', '``'), '`'),
                           CONCAT('`', REPLACE(table_name, '`', '``'), '`'),
                           CONCAT('`', REPLACE(column_name, '`', '``'), '`')
                         FROM
                           information_schema.columns
                         WHERE
                           table_schema=DATABASE() AND data_type LIKE 'date%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  columnsLoop: LOOP
    FETCH cur INTO _schema, _table, _column;
    IF done THEN
      LEAVE columnsLoop;
    END IF;   

    SET @sql_update=CONCAT_WS(' ', 'UPDATE', CONCAT(_schema, '.', _table),
                                   'SET', _column, '=', '\'1900-01-01\'',
                                   'WHERE', _column, '=', '\'0000-00-00\'');

    PREPARE stmt FROM @sql_update;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP columnsLoop;

  CLOSE cur;
END//
DELIMITER ;

Please see an example here.

Answer 2

This is an old question but was running into a similar problem except I was trying to set the 0000-00-00 to NULL.

Was trying to query this

UPDATE tablename SET date_column = NULL WHERE date_column = '0000-00-00';

and was getting the following error :

Incorrect date value: '0000-00-00' for column 'date_column' at row 1

Turns out the following query without '' around the 0000-00-00 worked !

 UPDATE tablename SET date_column = NULL WHERE date_column = 0000-00-00;