1

I am trying to create a MySQL table as follows:

 CREATE TABLE customers (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> join_date DATE DEFAULT 'not CI',
    -> email_address VARCHAR(255),
    -> PRIMARY KEY (id)
    -> );

However, I get the following error message:

ERROR 1067 (42000): Invalid default value for 'join_date'

How do I create a table with the date field default value as "not CI"?

Alhpa Delta
  • 3,385
  • 4
  • 16
  • 31
  • You can't. `'not CI'` is not a date value. If you want to see this value instead of null you can do it in the front end but not in the table. – forpas Oct 26 '19 at 15:33
  • ok, I have a csv file from which I need to import data into MySQL database. It has this kind of values in the data field. What is the way to deal with it then? How do I import the file so that it can be read properly with that value? Or do I have to update the file first to remove that value? – Alhpa Delta Oct 26 '19 at 15:34

1 Answers1

0

You can't do this, your default value is not a date and the date field must follow the default mysql formatting.

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

Guilherme Martin
  • 837
  • 1
  • 11
  • 22
  • ok, I have a csv file from which I need to import data into MySQL database. It has this kind of values in the data field. What is the way to deal with it then? How do I import the file so that it can be read properly with that value? Or do I have to update the file first to remove that value? – Alhpa Delta Oct 26 '19 at 15:36
  • you have to this https://stackoverflow.com/a/43890573/5193536 – nbk Oct 26 '19 at 15:55