0

In MySQL 8.0.28 we have a table column named expiry_date as datetime filed.

When we try to insert data greater than 15 years, it fires

ERROR 1292: 1292: Incorrect datetime value: '2038-01-20 03:36:33' for column 'expiry_date' at row 1

For 2038-01-19 03:36:33 date, its inserting to DB.

Table ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

We can not change data-type as this table is in production system.

What is causing this issue? Following threads were checked, but not addressing our issue :

incorrect-datetime-value-while-saving-data-to-mysql

incorrect-datetime-value-database-error-number-1292

error-in-mysql-when-saving-data

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
  • 1
    32 bits field ? – Ptit Xav Feb 06 '23 at 07:08
  • 1
    here is another one https://stackoverflow.com/questions/40924274/incorrect-datetime-value-for-future-date-in-mysql – Buggies Feb 06 '23 at 07:10
  • 1
    In addition to previous comment please review https://dev.mysql.com/doc/refman/8.0/en/datetime.html , it seems your column is timestamp datatype – P.Salmon Feb 06 '23 at 07:27
  • 1
    The supported DATETIME range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. There is no such problem on this datatype: https://dbfiddle.uk/3FJdRKs2 Show complete output for `SHOW CREATE TABLE your_table_name;`. – Akina Feb 06 '23 at 08:38
  • 1
    @P.Salmon TIMESTAMP should fail on both values. – Akina Feb 06 '23 at 08:39

0 Answers0