How can i change SQL date format to: 12th Jun 2021. (as example)

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;

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)