So, I’m trying to insert data from .csv file, but because of the date format I’m using – import causes it to set to 00-00-0000
import settings: format – CSV using load data (otherwise it causing errors and failing to import) Specific format options: none
Errors I’m receiving after import:
- Data truncated for column… my_date sets to 0000-00-00, even with ‘DD/MMM/yyyy’ date format,
my csv file structure: (just in case if i need to use specific format)
name;1;1st Jan 2021;2st Jan 2021;;;;;;;;;;;;;
Technically, I could format it manually to 12 jun 2021 / 12.06.2021 / 12/06/2021 but I would like to avoid that.
Sorry if it’s a dumb question and the answer is simple, but I have no idea how to fix it. 🙂
I already tried this but still nothing.
SET lc_time_names = 'en_US';
select date_format(my_date, 'DD MMM yyyy') FROM table1;
select date_format(my_date, 'DD/MMM/yyyy') FROM table1;
Advertisement
Answer
Maybe you should import your date columns as text data type, and make transformations later! Please follow this steps:
Import Wizard
Then Update your columns:
UPDATE myimport
SET StartDate = STR_TO_DATE(StartDate,'%D %b %Y');
UPDATE myimport
SET FinishDate = STR_TO_DATE(FinishDate,'%D %b %Y');
Then Change data type to DATE(or preferably date time):
ALTER TABLE myimport
MODIFY COLUMN StartDate DATE,
MODIFY COLUMN FinishDate DATE;
If we check the data,
mysql> SELECT * FROM myimport;
+------+------+------------+------------+
| name | Row | StartDate | FinishDate |
+------+------+------------+------------+
| name | 1 | 2021-01-01 | 2021-01-02 |
+------+------+------------+------------+
1 row in set (0.00 sec)
If we check the datatype to ensure that It is date:
SHOW CREATE TABLE myimport;
+----------+------------------------------------------------------
| Table | Create Table |
+----------+------------------------------------------------------
| myimport | CREATE TABLE `myimport` (
`name` text,
`Row` int DEFAULT NULL,
`StartDate` date DEFAULT NULL,
`FinishDate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+------------------------------------------------------
1 row in set (0.00 sec)