Incorrect datetime value Database Error Number: 1292

Question

Incorrect datetime value 0000-00-00 00:00:00 +0000 Database Error Number: 1292

Hi Everyone I'm having a problem a with a server upgrade done by my hosting company and I'm trying to understand what is occurring so i can fix the problem

My sever has recently been upgraded to Server version: 5.6.17 and I'm getting errors all over the place saying my datetime value is incorrect?

It seem to be add +0000 to the end of the datetime but I'm not sure why. This used to work perfectly fine on 5.5 but a recent upgrade has affected how my timestamps work

Error Number: 1292

Incorrect datetime value: '2014-04-02 08:49:43 +0000' for column 'created' at row 1

INSERT INTO `activitylog` (`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) VALUES ('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43 +0000')

If I modify this sql query without +0000 it works?

It affects anything that is a type of DATETIME on my table.

Has anyone else had a similar problem and now what the solution is to get this to work. At the moment I'm thing I will have to change all my PHP functions to echo the Date/Time rather than me calling NOW() on the query string

possible duplicate of [error code 1292 incorrect date value mysql](http://stackoverflow.com/questions/14625191/error-code-1292-incorrect-date-value-mysql) — jmail, Apr 02 '14 at 09:42
Note dupe, that is about invalid date formatting, this is about zero time. — Smar, Apr 28 '15 at 13:54

score 33 · Answer 1 · edited Jun 20 '20 at 09:12

I discovered after upgrading to MySQL 5.7 that this error started occurring in random situations, even when I wasn't supplying a date in the query.

This appears to be because previous versions of MySQL supported dates like 0000-00-00 00:00:00 (by default) however 5.7.4 introduced some changes to the NO_ZERO_DATE setting. If you still have old data present when using a newer MySQL version, then random errors may crop up.

I needed to perform a query like this to reset all the zero dates to another date.

# If the columns supports NULL, use that
UPDATE table SET date_column = NULL WHERE date_column < '1000-01-01';

# Otherwise supply another default date
UPDATE table SET date_column = '1970-01-01' WHERE date_column < '1000-01-01';

Alternatively, you may be able to adjust the NO_ZERO_DATE setting, although note what the docs say about it:

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

For me, the problem was that my `where` was incorrectly numeric when it should have been a string. I was trying to run `DB::table('contacts')->where('adId', 8888544168061)->update(['campaignId' => 8888626793661, 'adsetId' => 8888631774261]);` instead of `DB::table('contacts')->where('adId', '8888544168061')->update(['campaignId' => 8888626793661, 'adsetId' => 8888631774261]);` — Ryan, Jul 24 '17 at 19:03
it did happen to me after upgrading MySQL...but I don't understand your answer... — Francesco, Jan 11 '18 at 22:27
This is related to the `NO_ZERO_DATE` setting but there's more to the story, specifically how changes were made to `STRICT_TRANS_TABLES` and how it relates to the other "ZERO" settings as discussed in the release notes for [MySQL version 5.7.8](https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-sql-mode). If you're on MySQL 5.7.8 or later, you'll probably find that removing `STRICT_TRANS_TABLES` from `SQL_MODE` solves the problem, as @RDVS mentioned in his answer. — Russell G, Oct 20 '20 at 13:37

score 30 · Answer 2 · answered Jul 15 '17 at 01:26

30

Ok, so I was having this same error. What I did to fix it was use these lines of code to query the database I was having issues with:

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
SET sql_mode = '';
SET GLOBAL sql_mode = '';

The first line of code (SELECT) is to see what the current setting are for both 'SESSION' and 'GLOBAL'. Once you set them both to empty strings and run the select again, they should return nothing (be empty).

You may also need to use SET SESSION sql_mode = ''; but this resolved the issue for me. Basically one of the settings in there was jacking up the way the date was coming into the database (I was getting it in a 'YYYY-MM-DD HH:MM:SS AM/PM' format). Deleting NO_ZERO_IN_DATE and the other date option didn't help me.

My site is working like it's supposed to now. Hopefully this helps.

answered Jul 15 '17 at 01:26

callmejed

400
3
5

This solution worked for me (deleting `NO_ZERO_IN_DATE` didn't help). I had to drop and recreate the database, just in case that helps someone (I was trying to restore a database backup for a WordPress site) – grandmaestr Aug 11 '20 at 21:41
I believe by setting `SQL_MODE` to an empty string, you're removing `STRICT_TRANS_TABLES`, which is the actual setting causing the problem, as @RDVS mentioned in his answer. – Russell G Oct 20 '20 at 13:22
yous saved my life – Hafiz Siddiq Feb 01 '21 at 09:45
SET SESSION sql_mode = ''; fixed it for me - thanks. – Owl Sep 07 '21 at 10:58
This was the final solution to my prooblem after i commited the rest like NO_ZERO_DATE and hadnt worked. – NetPlayer Feb 07 '22 at 22:36

score 12 · Answer 3 · answered Mar 06 '20 at 08:15

12

I was facing same issue.

I run below command and it worked for me.

SET SESSION SQL_MODE='ALLOW_INVALID_DATES'

answered Mar 06 '20 at 08:15

Omkar

3,253
3
20
36

1

This worked for me, but maybe not for the reason you might think. If I append `ALLOW_INVALID_DATES` to the existing `SQL_MODE` value, it doesn't fix the problem. But if I remove `STRICT_TRANS_TABLES` from `SQL_MODE` (as @RDVS mentioned in his answer) or just set `SQL_MODE` to an empty string, it does work. So I don't believe `ALLOW_INVALID_DATES` is actually doing anything in this case other than the fact that the value doesn't include `STRICT_TRANS_TABLES`. – Russell G Oct 20 '20 at 13:19
1

This worked for me, and with the bonus of it being runnable within the context of a script to avoid having conf changes prior to making a connection. – bsplosion Jul 27 '21 at 12:55

score 11 · Answer 4 · edited Apr 28 '15 at 13:51

11

Short answer - NOW() in your query should work perfectly well with a MySQL DATETIME column.

Longer answer - I'm not sure how you ever saw +0000 working. The DATETIME column is formatted as 'YYYY-MM-DD HH:MM:SS'. When it comes to timezone differences, it's generally something you need to handle programmatically. MySQL does convert local times to UTC and back again when storing and retrieving TIMESTAMP data - but it doesn't do this with DATETIME or other Date / Time columns.

edited Apr 28 '15 at 13:51

Smar

8,109
3
36
48

answered Apr 02 '14 at 09:38

Ragdata

1,156
7
16

1

This is the thing i haven't done anything. Like i said for ages I have been abale to get this working on Server version 5.5, Never had a problem but then a recent upgrade to 5.6.17 has thrown this error – Luke O'Regan Apr 02 '14 at 09:47
I have the exact same problem. It has been working. Shared host probably update mysql, and now BOOM: Internal Server Error: 22007, 1292, Incorrect datetime value: '2016-09-12 18:30:00 UTC' for column 'xxxxxxx' at row 1... problem is I don't even have the 'UTC' in the string, it's a part of the mysql error message... – Raf A. Sep 13 '16 at 17:14
Exactly, I got this with a `timestamp` column type on an inherited code. It was gone after I switched the column type to `datetime`. – arntg Nov 30 '19 at 11:45

score 9 · Answer 5 · answered Aug 26 '18 at 17:57

Original my.cnf had sql_model set as follows:

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

I tried removing NO_ZERO_IN_DATE and NO_ZERO_DATE but it had no effect. But if I removed all terms (sql_mode empty) the error went away.

I went back to original sql_mode and thought I would remove terms 1-by-1 to see which one was cause. First attempt was to remove STRICT_TRANS_TABLES:

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

No error. So it appears that STRICT_TRANS_TABLES was the cause.

This is what worked for me. The changes made to `STRICT_TRANS_TABLES` and how it relates to the other "ZERO" settings are discussed in the release notes for [MySQL version 5.7.8](https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-sql-mode). — Russell G, Oct 20 '20 at 13:29

score 5 · Answer 6 · edited Jun 20 '20 at 09:12

Incorrect datetime value Database Error Number: 1292

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

you should use this type in: DateTime format

INSERT INTO `activitylog` 
(`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) 
VALUES 
('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43')

https://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

https://dev.mysql.com/doc/refman/5.0/en/datetime.html

http://bugs.mysql.com/bug.php?id=70188

update: 1

you should remove the space like your code '2014-04-02 08:49:43 +0000' and change the code like '2014-04-02 08:49:43+0000' as full query is following as:

INSERT INTO `activitylog` 
(`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) 
VALUES ('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43+0000')

look here : http://sqlfiddle.com/#!2/a2581/23099

FWIW, this is caused by strict SQL setting: [`NO_ZERO_IN_DATE`](https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_zero_date) — Smar, Apr 28 '15 at 13:52
Just remove 'NO_ZERO_DATE,NO_ZERO_IN_DATE' from your my.cnf or my.ini — user1283182, Jul 12 '16 at 13:18

score 0 · Answer 7 · answered Oct 16 '22 at 19:11

0

I found that this query works from MySQL Incorrect datetime value: '0000-00-00 00:00:00' if you have this problem

UPDATE activitylog SET created = NULL
WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00';

answered Oct 16 '22 at 19:11

Karim Samir

1,470
17
17

score -1 · Answer 8 · answered Apr 03 '22 at 02:41

I was facing similar issue but when I applied the code posted by @JedtheMarine as seen below, it resolved my case.

My case was specifically

Error updating record: incorrect datetime value '2022-04-03 12:00:00 AM' for column 'XXX' at row 39

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
SET sql_mode = '';
SET GLOBAL sql_mode = '';

Incorrect datetime value Database Error Number: 1292

8 Answers8

Incorrect datetime value Database Error Number: 1292

update: 1

Linked

Related