1292: Incorrect datetime value: '' for column at row 1

Question

I am trying to load data from excel sheet to the below table on MYSQL 5.6 on windows 8.1 and I am getting 'Incorrect datetime value:' error.

Term date column is of DATETIME data type and the data has null values in the excel sheet which I am trying to insert into the table.

I did some research and found that the problem is with the SQL strict mode. But I am not able to figure out how to disable or modify the SQL strict mode.

I in fact tried this SET SESSION sql_mode='ALLOW_INVALID_DATES' but no luck.

Some said editing my.ini file in installation directory will help but I am not able to find it in the installation directory.

can any one please help me resolve this issue.

create table EMPLOYEE(EMP_ID integer(10),
EMP_NAME char(25),
SALARY integer(25),
START_DATE datetime,
TERM_DATE datetime DEFAULT '1900-01-01',
PRIMARY KEY (EMP_ID));

Error Message:

15:23:08    INSERT INTO `mith`.`EMPLOYEE` (`EMP_ID`, `EMP_NAME`, `SALARY`, `START_DATE`, `TERM_DATE`) VALUES ('26', 'Will Banker', '90000', '00:00.0', '')  1292: Incorrect datetime value: '' for column 'TERM_DATE' at row 1  

Gonophore

Why are you trying to set a date to 00:00.0 instead of to a real date? – Curricle

The excel from which I am trying to load data has the date values but MYSQL import is not taking those values as date values. – Gonophore

Answer 1

In MySQL, '' (an empty string) is different from a null. If you want a null value, you should use an explicit null:

INSERT INTO `mith`.`EMPLOYEE` 
(`EMP_ID`, `EMP_NAME`, `SALARY`, `START_DATE`, `TERM_DATE`)
VALUES ('26', 'Will Banker', '90000', '00:00.0', null)

Steelyard

I agree. But I am not actually executing insert statement. I am using MYSQL import data option to load data from excel. So it is picking all null values as ''. – Gonophore

This was different in old versions of mysql. I ran into that too after migration. Using null instead of '' solved that issue. – Carduaceous

Answer 2

I managed to solve a similar problem using the commands:

mysql> set session sql_mode='';

and

mysql> set global sql_mode='';

in mysql prompt.

Conceptualism

Answer 3

Try replacing the null values in Excel with 0000-00-00. I have not tried this with Excel, but it works for csv file. import

Gurkha