Error Code 1292 Incorrect Date Value MySQL

error code 1292 incorrect date value mysql

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', 'info@hcentrale.it', 'http://www.hcentrale.it/', 'Trento', 'TN')

MySQL Error 1292 Incorrect date value?

You are using 'YYYY-dd-MM' format whereas you need to use 'YYYY-MM-dd', try the following:

INSERT INTO orders VALUES (1, 1200, '2013-10-23', '2013-10-28');

Error Code: 1292. Incorrect date value: '0000-00-00' for date column

Running MySQL Workbench as Administrator and running this query resolved it for me:

SET GLOBAL sql_mode = '';

For some reason updating the my.ini with the following did not take care of it.

sql_mode = ''

#1292 - Incorrect date value: '0000-00-00'

The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation.

For more information read this.

Hope it helps.

Mysql error: 1292 Incorrect datetime value showing a date that doesn't exist

The problem is that you don't have quotes around full_date in the query string you are creating. So, you are getting something like:

VALUES ( 
        DATE_FORMAT( 1900-04-01, "%Y%m%d"),'
        1900-04-01,
        . . . 

This is different from:

VALUES ( 
        DATE_FORMAT('1900-04-01', "%Y%m%d"),'
        1900-04-01,
        . . . 

Your version does an arithmetic calculation (1900 - 04 - 01 = 1895), so it turns into:

VALUES ( 
        DATE_FORMAT( 1895, "%Y%m%d"),'
        1895,
        . . . 

By the way, I'm guessing that this would have been quite obvious if you had looked at the string after variable substitution. Whenever you use dynamic SQL, you should always be looking at the query strings being produced.

You can fix this by putting single quote delimited throughout your statement. Or, better yet, parameterize the statement and use EXECUTE . . . USING (see here).

EDIT:

You can write the query in the form:

insert into table_name(. . . )
    select DATE_FORMAT(full_date, '%Y%m%d), 
           full_date,
           YEAR(full_date),
           . . .
    from (select ? as full_date, . . .) t;

Each parameters goes in the subquery. The values can then be used in the outer query.

How to fix MySQL 8 error codes : 1525 and 1292?

The lesser evil is possibly to change the session-wide SQL mode for both the migration script and the affected application—that should be a simple one time change (as long as the connection code is not copy+pasted in a hundred places). The mode that allows invalid dates is ALLOW_INVALID_DATES:

SET @@SESSION.sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'ALLOW_INVALID_DATES');

Full demo:

mysql> CREATE TABLE foo (bar DATE);
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO foo (bar) VALUES ('2019-02-30');
ERROR 1292 (22007): Incorrect date value: '2019-02-30' for column 'bar' at row 1
mysql> SET @@SESSION.sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'ALLOW_INVALID_DATES');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO foo (bar) VALUES ('2019-02-30');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM foo;
+------------+
| bar        |
+------------+
| 2019-02-30 |
+------------+
1 row in set (0.00 sec)

Error code 1292 incorrect Date | Time

Try this out:

INSERT INTO comments values ('lars', 'myemail@gmail.com','http://www.vogella.com', '2009-09-14', 'Summary','My first comment');

You seem to be sending a datetime while your field is a date. Probably you should change your table structure from date to datetime.

---

Related Topics