I'm trying to create a movie databse in MYSQL (I use MYSQL workbench).
I use TxtToMy (link) to import the ratings.csv-file generated by imdb.com into MYSQL.
Now the problem is that 16 movies (out of 819) can not be imported because the 'date_released' is not in the correct format.
This error shows in the log:
(#22007) Incorrect date value: '1983-05' for column 'release_date' at row 1
Now I wonder if there is way to alter the table in MYSQL to automatically change any date in the format YYYY-MM to YYYY-MM-DD with DD being 00 or something. Or so that is also accepts dates in different formats.
I don't know TxtToMy very well, but if you have to use it, and if it won't allow you to implement a solution like didierc proposed above, then one option would be to add a VARCHAR column for importing the raw date and use a trigger to update the "real" date column.
Assuming your table is named MOVIES...
ALTER TABLE MOVIES ADD (RawDateFromIMDB VARCHAR(10));
Then change your TxtToMy definition to import into RawDateFromIMDB instead of release_date.
This trigger will take the raw date and use it to update your release_date column:
CREATE TRIGGER Movie_RawDateParse
BEFORE INSERT ON Movies
FOR EACH ROW
BEGIN
SET NEW.release_date = str_to_date(NEW.RawDateFromIMDB, '%Y-%m-%d');
END;
MySQL seems perfectly happy to have a date like '1983-05-00' so this worked for me when I "imported" the value 1983-05 into RawDateFromIMDB. It also worked for a "regular" date, as you can see below:
mysql> INSERT INTO Movies (RawDateFromIMDB) values ('1983-05');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO Movies (RawDateFromIMDB) values ('1983-06-22');
Query OK, 1 row affected (0.04 sec)
mysql> SELECT release_date, RawDateFromIMDB FROM Movies;
+--------------+-----------------+
| release_date | RawDateFromIMDB |
+--------------+-----------------+
| 1983-05-00 | 1983-05 |
| 1983-06-22 | 1983-06-22 |
+--------------+-----------------+
2 rows in set (0.00 sec)