error code 1292 incorrect date value mysql

Question

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 ACTION
  ON UPDATE CASCADE,
CONSTRAINT `Comune`
  FOREIGN KEY (`Comune` )
  REFERENCES `PROGETTO`.`PAESE` (`Comune` )
  ON DELETE NO ACTION
  ON 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)

Rebbeccarebe

data_apertura field is DATE type and must be like 2013-01-31 – Hectorhecuba

Date and Time Literals – Ephemeris

Answer 1

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')

Latanya

Answer 2

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!

Litho

For windows it's my.ini – Supreme

Thank you you helped me a lot, especially when i am working on legacy code with new config – Lactam

I had to add the line to /etc/mysql/mysql.conf.d/mysqld.cnf instead. So sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf – Sholes

Answer 3

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");

Paratuberculosis

Answer 4

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.

Openminded

Answer 5

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.

Haith

Answer 6

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"

Johen