Error code 1292 incorrect date value mysql

I have a table 

`CREATE TABLE IF NOT EXISTS `PROGETTO`.`ALBERGO` (`ID` INT(11) NOT NULL COMMENT 'identificativo dell\' albergo' ,`nome` VARCHAR(45) NULL COMMENT 'Il nome dell\'albergo' ,`viale` VARCHAR(45) NULL COMMENT 'Il viale in cui si trova ' ,`num_civico` VARCHAR(5) NULL COMMENT 'Il numero civico che gli appartiene' ,`data_apertura` DATE NULL COMMENT 'Data di inizio apertura (inizio stagione)' ,`data_chiusura` DATE NULL COMMENT 'Data di chiusura (fine stagione)' ,`orario_apertura` TIME NULL COMMENT 'Orario di apertura' ,`orario_chiusura` TIME NULL COMMENT 'Orario di chiusura' ,`posti_liberi` INT(11) NULL COMMENT 'Disponiblità posti liberi ' ,`costo_intero` FLOAT NULL COMMENT 'Costo del prezzo intero' ,`costo_ridotto` FLOAT NULL COMMENT 'Costo del prezzo ridotto' ,`stelle` INT(11) NULL COMMENT 'Classificazione in base al criterio delle stelle' ,`telefono` VARCHAR(15) NULL COMMENT 'Recapito telefonico' ,`mail` VARCHAR(100) NULL COMMENT 'Recapito e-mail' ,`web` VARCHAR(100) NULL COMMENT 'Sito Web relativo all\'ente' ,'Nome-paese` VARCHAR(45) NOT NULL COMMENT 'Identificativo del paese in cui si trova l\'albergo' ,`Comune` CHAR(2) NOT NULL COMMENT 'Identificativo del comune in cui si trova l\'albergo' ,PRIMARY KEY (`ID`) ,INDEX `Nome-paese` (`Nome-paese` ASC) ,INDEX `Comune` (`Comune` ASC) ,CONSTRAINT `Nome-paese`FOREIGN KEY (`Nome-paese` )REFERENCES `PROGETTO`.`PAESE` (`Nome-paese` )ON DELETE NO ACTIONON UPDATE CASCADE,CONSTRAINT `Comune`FOREIGN KEY (`Comune` )REFERENCES `PROGETTO`.`PAESE` (`Comune` )ON DELETE NO ACTIONON UPDATE CASCADE)ENGINE = InnoDB

When i try to run this query:

INSERT INTO `PROGETTO`.`ALBERGO`(`ID`, `nome`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `posti_liberi`, `costo_intero`, `costo_ridotto`, `stelle`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES(0, 'Hotel Centrale', 'Via Passo Rolle', '74', '01-05-2012', '31-09-2012', '06:30', '24:00', 80, 50, 25, 3, '43968083', '[email protected]', 'http://www.hcentrale.it/', 'Trento', 'TN')

*Error Code: 1292. Incorrect date value: '01-05-2012' for column 'data_apertura' at row 1*

What have i to change? (i tried to change format's date from gg/mm/yyyy to gg-mm-yyyy, but nothing changed)

6

Best Answer

With mysql 5.7, date value like 0000-00-00 00:00:00 is not allowed.

If you want to allow it, you have to update your my.cnf like:

sudo nano /etc/mysql/my.cnf

find

[mysqld]

Add after:

sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart mysql service:

sudo service mysql restart

Done!

Insert date in the following format yyyy-MM-dd example,

INSERT INTO `PROGETTO`.`ALBERGO`(`ID`, `nome`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `posti_liberi`, `costo_intero`, `costo_ridotto`, `stelle`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES(0, 'Hotel Centrale', 'Via Passo Rolle', '74', '2012-05-01', '2012-09-31', '06:30', '24:00', 80, 50, 25, 3, '43968083', '[email protected]', 'http://www.hcentrale.it/', 'Trento', 'TN')

I was having the same issue in Workbench plus insert query from C# application. In my case using ISO format solve the issue 

string value = date.ToString("yyyy-MM-dd HH:mm:ss");

I happened to be working in localhost , in windows 10, using WAMP, as it turns out, Wamp has a really accessible configuration interface to change the MySQL configuration. You just need to go to the Wamp panel, then to MySQL, then to settings and change the mode to sql-mode: none.(essentially disabling the strict mode) The following picture illustrates this.

enter image description here

An update. Dates of the form '2019-08-00' will trigger the same error. Adding the lines:

[mysqld]sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to mysql.cnf fixes this too. Inserting malformed dates now generates warnings for values out of range but does insert the data.

Just an update of @Sinan Eldem's answer:

Since MySQL 8.0 the sql_mode='NO_AUTO_CREATE_USER' is deprecated (source) and should be removed from the line, otherwise it will result in error reading sql_mode and failure starting mysqld.

If using MySQL 8.0+ add the following line instead:

[mysqld]sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Random Posts

Entity framework vs NHibernate - PerformanceHow to return a specific element of an array?How to create SageMaker Studio environment from CLI?How to drop a list of rows from Pandas dataframe?Trying to understand implementation of gaussian blurring in matlabHow to use Sub Function in Rfirebase deploy --only functions overrides existing functionsSort alphabetically and get only first of every letter of the alphabeterror code 1292 incorrect date value mysqlRegular expression for not allowing spaces in the input fieldwhat's the typeof(jQuery)fputs() newline behaviour in cC++ - Nested include - Avoiding 'include nested too deeply error'JPS incremental annotation processing is disabled. Compilation results on partial recompilation may be inaccurateHelm Secrets Unable To Recognize DataVariant with unique typesWhy is iCloud blocking some of my applications emails?C: String functions without string.h libraryMySQL: Query value of (global) variableHow can I randomly select (choose) an item from a list (get a random element)?