title: Mysql error: 1292 Incorrect datetime value showing a date that doesn't exist
body: <p>I'm getting an error with this stored procedure:</p>
  
  <pre><code>DELIMITER $$
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `populateTimeTable`(IN table_name VARCHAR(45), IN start_date DATE, IN end_date DATE)
  BEGIN
  
  DECLARE full_date DATE;
  
  SET @query_create = CONCAT('CREATE TABLE IF NOT EXISTS ' ,table_name, '(
      idDate INT(8) UNSIGNED NOT NULL,
      date DATE NOT NULL,
      year SMALLINT(4) UNSIGNED NOT NULL,
      quarter TINYINT(1) UNSIGNED NOT NULL,
      month tinyint(2) unsigned not null,
      month_name ENUM(''January'',''February'',''March'',''April'',''May'',''June'',''July'',''August'',''September'',''October'',''November'',''December'') NOT NULL,
      month_name_spanish ENUM(''Enero'',''Febrero'',''Marzo'',''Abril'',''Mayo'',''Junio'',''Julio'',''Agosto'',''Septiembre'',''Octubre'',''Noviembre'',''Diciembre'') NOT NULL,
      week TINYINT(2) UNSIGNED NOT NULL,
      day TINYINT(2) UNSIGNED NOT NULL,
      weekday TINYINT(1) UNSIGNED NOT NULL,
      weekday_name ENUM(''Monday'',''Tuesday'',''Wednesday'',''Thursday'',''Friday'',''Saturday'',''Sunday'') NOT NULL,
      weekday_name_spanish ENUM(''Lunes'',''Martes'',''Miércoles'',''Jueves'',''Viernes'',''Sábado'',''Domingo'') NOT NULL,
      PRIMARY KEY(idDate)
  )');
  PREPARE stmt1 FROM @query_create;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;
  
  SET @query_delete = CONCAT('DELETE FROM ' ,table_name);
  PREPARE stmt2 FROM @query_delete;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
  
  SET full_date = start_date;
  
  WHILE full_date &lt;= end_date
  DO
      SET @query_insert = CONCAT('INSERT INTO ' ,table_name, '(
          idDate,
          date,
          year,
          quarter,
          month,
          month_name,
          month_name_spanish,
          week,
          day,
          weekday,
          weekday_name,
          weekday_name_spanish
      ) VALUES ( 
          DATE_FORMAT(' ,full_date, ', "%Y%m%d"),'
          ,full_date, ',
          YEAR(' ,full_date, '),
          QUARTER(' ,full_date, '),
          MONTH(' ,full_date, '),
          MONTHNAME(' ,full_date, '),
          (SELECT CASE MONTH(' ,full_date, ')
              WHEN 1 THEN ''Enero''
              WHEN 2 THEN ''Febrero''
              WHEN 3 THEN ''Marzo''
              WHEN 4 THEN ''Abril''
              WHEN 5 THEN ''Mayo''
              WHEN 6 THEN ''Junio''
              WHEN 7 THEN ''Julio''
              WHEN 8 THEN ''Agosto''
              WHEN 9 THEN ''Septiembre''
              WHEN 10 THEN ''Octubre''
              WHEN 11 THEN ''Noviembre''
              WHEN 12 THEN ''Diciembre''
          END),
          WEEK(' ,full_date, ', 5),
          DAY(' ,full_date, '),
          WEEKDAY(' ,full_date, ') + 1,
          DAYNAME(' ,full_date, '),
          (SELECT CASE DAYOFWEEK(' ,full_date, ')
              WHEN 1 THEN ''Domingo''
              WHEN 2 THEN ''Lunes''
              WHEN 3 THEN ''Martes''
              WHEN 4 THEN ''Miércoles''
              WHEN 5 THEN ''Jueves''
              WHEN 6 THEN ''Viernes''
              WHEN 7 THEN ''Sábado''
          END)
      )');
      PREPARE stmt3 FROM @query_insert;
      EXECUTE stmt3;
      DEALLOCATE PREPARE stmt3;
  
      SET full_date = DATE_ADD(full_date, INTERVAL 1 DAY);
  END WHILE;
  END
  </code></pre>
  
  <p>IF I execute:</p>
  
  <pre><code>CALL populateTimeTable('dim_time','1900-04-01','1901-01-01')
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>Error Code: 1292 Incorrect datetime value: '1895'
  </code></pre>
  
  <p>Removing all the prepared statements and using a fixed table name, it works, so the problem is with the prepared statement stmt3.</p>
  
  <p>If I change the start date, I get the same error but with different value.</p>