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

So, im trying to insert data from .csv file, but because of the date format im 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 im recieving 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 awoid 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 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;

CodePudding user response：

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)

CodePudding user response：

You can transform and use the below query to transform the date.

select date_format(`created_at`, '%D %b %Y') FROM product_attributes;

Please see this pic for the query result.

You can use this SQL query to see the DB table query and insert query and use this for reference.