MySQL Invalid DateTime for dates older than 1980.

Question

While trying to import the .sql file into my database, I get 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 encountered this error only for dates older than 1980. And this only happens when I try to import a dump through an import statement or through a WorkBench. If I execute only the expression, it works fine. Here is the table structure and 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); 

Answer 1

Batchdate is not a DATETIME column, but a TIMESTAMP column. The range for TIMESTAMP does not include this date:

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

and error message

Invalid date and time value: '1936-01-31 00:00:00'

It is older than "1970-01-01 00:00:01", out of range of the TIMESTAMP data TIMESTAMP

DATE, DATETIME, and TIMESTAMP types

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

Decision

Change the data type to DATETIME .

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

If you are using MySQL 5.6.5 or later, you can change the data type to DATETIME, because since this version of DATETIME also supports automatic initialization. You seem to be using a newer version since you are using several of these columns with auto-initialization. This feature has been added at the same time.

Automatic initialization and update for TIMESTAMP and DATETIME

Starting with MySQL 5.6.5, the TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (i.e. the current timestamp). Until 5.6.5, this is true only for TIMESTAMP, and for no more than one TIMESTAMP column for each table.

Note

Date values ​​in the 1970s will also work.