Code: 1292 SQL State: 22007 --- Incorrect datetime value
-
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time. Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;How can I fix the problem?
-
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time. Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;How can I fix the problem?
-
The DATE type does not include a time value, see https://dev.mysql.com/doc/refman/8.0/en/datetime.html#:~:text=MySQL%20retrieves%20and%20displays%20DATE,%3Amm%3Ass%20'%20format.[^].
thanks for your quick answer date is field name not data type and its data type is DATETIME
SELECT
DATE(`MyDateColumn`) AS `Date`,
COUNT(`MyDateColumn`) AS `Record count`
FROM tbl_Pw
WHERE `MyDateColumn` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`MyDateColumn`)
ORDER BY `MyDateColumn`; -
thanks for your quick answer date is field name not data type and its data type is DATETIME
SELECT
DATE(`MyDateColumn`) AS `Date`,
COUNT(`MyDateColumn`) AS `Record count`
FROM tbl_Pw
WHERE `MyDateColumn` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`MyDateColumn`)
ORDER BY `MyDateColumn`; -
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time. Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;How can I fix the problem?
So summing up the other posts and from the OP. Basically it appears that your database itself has invalid data. How could that happen? Something pushed it in there before it was validated. Or perhaps there is some problem with timezones and/or an actual bug in the database itself. You might be able to fix a timezone problem but otherwise you would need to update the row to put a correct value in there. If it was me I would expect that there is other invalid data in that table and perhaps other tables with timestamps also. You would need to uniquely identify each row then craft an update statement to force that column to a correct (manually determined) value.
-
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time. Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;How can I fix the problem?