MySQL Incorrect DateTime Value for Date older than 1980

Question

While, trying to import a .sql file into my database, I am getting the following error for one of the insert statements -

ERROR 1292 (22007) at line 31504: Incorrect datetime value: '1936-01-31 00:00:00' for column 'BatchDate' at row 1. Operation failed with exitcode 1

I am facing this error only for dates older than 1980. and its happening only while I am trying to import the dump through import statement or through WorkBench. If I execute the statement alone, it works fine. Here is the table structure and the insert statement

DROP TABLE IF EXISTS `BatchEntry`;

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BTax_ID` DOUBLE NULL DEFAULT 0, 
  `BPayor_No` DOUBLE NULL DEFAULT 0, 
  `BBroker_No` DOUBLE NULL DEFAULT 0, 
  `BHam_Cont` VARCHAR(4), 
  `BInv_Org_Date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BInv_Due_Date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BDate_Adv` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BRec_Amt` DECIMAL(19,4) DEFAULT 0, 
  `BPaymnt_Com` LONGTEXT, 
  `BTrans_Count` INTEGER DEFAULT 0, 
  `BPrefix` VARCHAR(10), 
  `BStartNumber` INTEGER DEFAULT 0, 
  `BSuffix` VARCHAR(10), 
  `BCreated` TINYINT(1) DEFAULT 0, 
  `BAdvMethod` INTEGER DEFAULT 0, 
  INDEX (`BPayor_No`), 
  INDEX (`BTax_ID`), 
  PRIMARY KEY (`BatchNo`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

INSERT INTO `BatchEntry` (`BatchNo`, `BatchDate`, `BTax_ID`, `BPayor_No`, `BBroker_No`, `BHam_Cont`, `BInv_Org_Date`, `BInv_Due_Date`, `BDate_Adv`, `BRec_Amt`, `BPaymnt_Com`, `BTrans_Count`, `BPrefix`, `BStartNumber`, `BSuffix`, `BCreated`, `BAdvMethod`) VALUES (1396, '1936-01-31 00:00:00', 561986585, 4528, 749, 'BSR', '2005-12-30 00:00:00', '2006-01-30 00:00:00', '2006-01-31 00:00:00', 0, NULL, 14, 'MC', 24850, NULL, 1, 1);

Hightail

Answer 1

Batchdate is not a DATETIME column but a TIMESTAMP column. The range for TIMESTAMP doesn't include this date:

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- it's TIMESTAMP

and the error message is

Incorrect datetime value: '1936-01-31 00:00:00'

That is older than '1970-01-01 00:00:01', out of range of the data type TIMESTAMP

The DATE, DATETIME, and TIMESTAMP Types

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Solution

Change the data type to DATETIME.

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` DATETIME DEFAULT CURRENT_TIMESTAMP, 
  [...]

If you're using MySQL 5.6.5 or newer then you could change the datatype to DATETIME, because since this version DATETIME supports the automatic initialization too. You seem to use a newer version, since you're using more than one such column with automatic initialization. This feature was added at the same time.

Automatic Initialization and Updating for TIMESTAMP and DATETIME

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.

Note

For date values in the 1970s it will work too.

Frankish

Thanks you very much. It works!! However, what is the explanation behind the fact that it works when I execute the statement only and does not work while importing the dump. – Hightail

If you're executing the single command, you get your message as a warning and if you're selecting the row you will find that there's the special value '0000-00-00 00:00:00' that indicates an invalid value: Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'). from The DATE, DATETIME, and TIMESTAMP Types – Frankish