0

I have a nullable date column in a MySQL InnoDB table. How do I update that column to NULL when issuing an update?

UPDATE memo_codes_student SET `resolved_date_client` = NULL, `status` = 'Rejected' WHERE `guid_enrollment` = @guid_enrollment AND `guid` = @itemGUID

When I run the statement above, it updates the field to 0000-00-00. How can I get it to update the field to NULL?

codegr8
  • 5
  • 1
  • 4
  • Possible duplicate of [how to insert an empty value in mysql date type field?](http://stackoverflow.com/questions/1137655/how-to-insert-an-empty-value-in-mysql-date-type-field) – Gouda Elalfy Jan 18 '16 at 16:36
  • Thanks, but I'm trying to figure out how to UPDATE a date field to null rather than insert – codegr8 Jan 18 '16 at 16:39
  • date type doesn't have null value any way otherwise insert or update. – Gouda Elalfy Jan 18 '16 at 16:40
  • Please provide `show create table memo_codes_student` and `show variables like 'sql_mode';`. – Rick James Jan 18 '16 at 20:45
  • Interesting: [mySQL Docs](http://dev.mysql.com/doc/refman/5.7/en/using-date.html) The special “zero” date '0000-00-00' can be stored and retrieved as '0000-00-00'. When a '0000-00-00' date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date. – xQbert Jan 18 '16 at 20:53

1 Answers1

1

You are probably running in NO_ZERO_DATE mode.

See this post for more information: https://stackoverflow.com/a/3891947/1602014

Community
  • 1
  • 1
Chris Fremgen
  • 4,649
  • 1
  • 26
  • 26