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

Question

I referenced many questions which have the same title as mine, but they have a different approach and different issue so this question is not a duplicate.

I have a table in which column fm_sctrdate is a date type and has the default value 0000-00-00.

Insertion by website is working fine but when I try to insert any value by phpmyadmin then I got following error.

Mysql version is 5.7.11. One more thing recently our server has been upgrade from mysqlnd 5.0.12 to 5.7.11.

Here is the query

INSERT INTO `iavlif_fmp_clientquote` (`jm_cqid`, `fmsq_id`, `fmsg_id`, 
`fm_sctrdate`, `fm_sctrtime`, `fm_sctbaggage_weight`,     
`fm_sctfreight_weight`, `fm_sctpassenger`, `fm_sctinfant`, 
`fm_sctinfant_details`, `fm_sctinfant_dob`, `fmtp_id`, `fmtpi_id`,
`jmcnt_id`, `fm_name`, `fm_company`, `fm_email`, `fm_phone`, `fmts_id`,
`jmts_id`, `fm_pax_nbr`, `fm_hours_nbr`, `fmqt_id`, `fmtr_id`,
`fm_sctnotes`, `fm_locdepart`, `fm_locarrive`, `fm_sctconsignment_weight`,
`fm_sctconsignment_dimensions`, `fm_sctconsignment_desc`, `fm_sctdangerous`,
`fm_scturgent`, `fm_sctspecial_instructions`, `fm_sctquote_type`,
`fm_sctwork_type`, `fm_sctreoccuring`, `fm_sctaccommodation`, `fm_sctcar`,
`fm_recdate`, `fm_recenddate`, `fm_recfrequency`, `fm_rectime`,    
`fm_medical`, `fm_medical_details`, `fm_user_ip`, `fm_dang_details`,
`fm_sctsubstance`, `fm_sctpurchase_number`, `fm_role_id`, `fm_myrole_id`,
`jm_myrole_id`, `fm_sctwork_point`, `fm_locdepartarrive`, `fm_sctnbr`, 
`fm_dateCreated`, `fm_cc`, `fm_gl`, `fm_timeCreated`, `jm_qtid`, `jmtp_id`,
`jmtpi_id`, `jmsg_id`, `jms_id`, `jmsq_id`, `fms_id`, `fmcq_id`) VALUES
(NULL, '1', '1', '0000-00-00', '1', '1', '1', 'sdfasd', 'No',
'sdafdsafdsaf', 'dsfas', 'sdfasd', 'dsafds', '0', 'asdfds', 'sdfasd',
'sdfads', 'sdaf', 'sdaf', 'sdaf', '0', '0', '0', '1sadfasdsda', 'sdfadsf',
'as', 'as', 'as', 'asas', 'asd', 'No', 'No', 'adsfsd', 'eqwrqew', 'qewrqew',
'No', 'No', 'No', '0000-00-00', '0000-00-00', 'ewqr', 'qewrw', 'No', 'eqwr',
'ewqr', 'qewr', '', '', '', '', '', '', '', '0', '0000-00-00', '', '',
'00:00:00.000000', '', '', '', '', '', '', '', NULL)

We started facing this issue just after upgrade of mysql

from phpmyadmin for date field are you typing date or select from default date picker? — Denis Bhojvani, May 18 '16 at 07:21
I am leaving date picker as it is. Neither changing date nor entering date by manually — urfusion, May 18 '16 at 07:23
Not the solution but I suggest using `NULL` instead of `0000-00-00` since this is not a valid date. — Peter, May 18 '16 at 07:25
@Peter: When setting default value to `Null` then I got `#1067 - Invalid default value for 'fm_recdate'` — urfusion, May 18 '16 at 07:27
That is because you need to insert either `NULL` or an empty value. — Peter, May 18 '16 at 07:32
actually i have tryed with this default value as you it is working fine in my local phpmyadmin — Denis Bhojvani, May 18 '16 at 07:33
@DineshBhojvani : yes, it is working fine on my local system. But not on server. — urfusion, May 18 '16 at 07:34
hello have read this may it helps you https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-11.html — Denis Bhojvani, May 18 '16 at 07:40
@KuKeC : you can propose answer if you want I will accept it. — urfusion, May 18 '16 at 10:52

score 76 · Accepted Answer · edited Dec 08 '22 at 22:38

76

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

To disable strict mode and other strict features, run this query:

SET GLOBAL sql_mode = '';

For more information read this.

Hope it helps.

edited Dec 08 '22 at 22:38

rmobis

26,129
8
64
65

answered May 18 '16 at 10:56

KuKeC

4,392
5
31
60

9

So what is the solution? :/ – TheRealChx101 Jan 03 '21 at 19:12
18

An answer should include the solution, not just a link to it. See [Are answers that just contain links elsewhere really “good answers”?](https://meta.stackexchange.com/q/8231/217657) – Melebius Mar 02 '21 at 08:54
1

The solution is to execute `SET sql_mode = '';` – Sych Jun 23 '22 at 11:48
This work for me SET GLOBAL sql_mode = ''; – kantsverma Sep 27 '22 at 10:11

Amit Garg · Answer 2 · 2022-10-25T10:48:14.477

25

You have 3 options to make your way:

The sql mode can be strict mode. Go to mysql/bin/

open my.ini or my.cnf based on windows or linux

Change sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To sql_mode= ""

then restart mysql then set global sql_mode='';

Select NULL from the dropdown to keep it blank.
Select CURRENT_TIMESTAMP to set current datetime as default value.

edited Oct 25 '22 at 10:48

answered Nov 21 '16 at 17:08

Amit Garg

563
5
6

Using HeidSQL, I could change the variable name sql_mode and change this: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. And I only need to remove NO_ZERO_IN_DATE,NO_ZERO_DATE. And it works. – Nicolas400 Mar 01 '23 at 03:18

score 18 · Answer 3 · edited Aug 26 '20 at 12:31

18

After reviewing MySQL 5.7 changes, MySql stopped supporting zero values in date / datetime.

It's incorrect to use zeros in date or in datetime, just put null instead of zeros.

edited Aug 26 '20 at 12:31

Salim Djerbouh

10,719
6
29
61

answered May 18 '16 at 09:28

TomerM

335
1
7

4

Yes, Mysql stop using zero values by default. But as @KuKec mentioned in comment by a link we can off strict mode and can use zero values. So If you add this information too in your answer I have no issue in accepting that. – urfusion May 18 '16 at 10:51
1

What if the column doesn't accept nulls? – Jay Bienvenu Mar 03 '23 at 18:32

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

3 Answers3

Linked

Related