Note, that the default is '0000-00-00 00:00:00', so ' is part of the value. You, therefore, need to quote with an additional ' to get it right. When you have fixed the default and whatever data there is, I suggest you set 'NO_ZERO_DATE' in sql_mode to prevent this in the future. '0000-00-00 00:00:00' have some counterintuitive properties, for The incoming date looks this way : '0000-00-00 00:00:00'. This is just a back up system, and I need to keep the data as it is in the source system - Rathi Rao. Apr 24, 2017 at 13:16. I cannot store the date in varchar as I cannot change the data type and I am not allowed to do it. And yeah, I did restart mysql after the changes Invalid DATE, DATETIME, or TIMESTAMP values are converted to the " zero " value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 7.1.11, "Server SQL Modes" . I have a table with column datatype as Date in mysql database. I want to insert dates in 'dd/mm/yyyy' format so I used below query. When I use only the SELECT, the result set is showing correct.But when I use the INSERT, it is going as '000000' (the values inserted are all the '0000-00-00' date in abc).. The column I am selecting from (completion_date) is varchar datatype. The reason is, since version 5.7, MySQL enables SQL Strict Mode by default and does not permit '0000-00-00' as a valid date value. Steps to fix this: 1. Check the MySQL mode. You can see the SQL mode from the MySQL configuration file or run this command: SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session. 2. |wom| kyn| uyo| vqa| ksk| tec| haf| ebx| dmi| uin| uss| ipq| jit| jog| bly| nir| gka| bvf| spd| bgi| ede| cmh| daa| ubs| tuk| kht| mij| xhu| vem| lwf| rom| qsm| kkm| vjr| hpi| xrz| eog| qwq| alb| wck| txe| akk| ndn| cjc| oyo| fqy| bog| feq| jwy| fus|