Question 21974 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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;
Tags:

2

Answers


  1. 0

    Maybe you should import your date columns as text data type, and make transformations later! Please follow this steps:

    Import Wizard

    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)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search