CODEWITHSUNDEEP
mysqlsqldatedatetimesql-update
stat77
stat77

Reputation: 113

Date Time Conversion in MySQL

I imported a CSV file into MySQL. The CSV file contains Date/Time in the format 'mm/dd/yyyy hh:mm'.

It gets imported into MySQL only as text. So I did import it as text. I then created another field hoping to convert this text value to date time in this format 'mm-dd-yyyy hh:mm' OR in this format 'YYYY-mm-dd hh:mm'. But it does not work. Below is my code.

ALTER TABLE table1 ADD COLUMN StartDateNEW DATETIME;
SET SQL_SAFE_UPDATES = 0;
UPDATE table1 SET StartDateNEW = STR_TO_DATE(StartDate, '%m/%e/Y %H:%i');
SET SQL_SAFE_UPDATES = 1;

Sample Data:

enter image description here

Some more sample data:

enter image description here

I have been trying this for over an hour now. Can someone please help?

Upvotes: 1

Views: 333

Answers (3)

Allen King
Allen King

Reputation: 2516

A quick hack is as follows:

  1. Open CSV File in Excel
  2. Select the date column
  3. Change date format to yyyy-mm-dd using cell format popup
  4. Save file
  5. Import to MySQL

This was you don't need to import in text form and then try to convert the data into DateTime.

Upvotes: 0

GMB
GMB

Reputation: 222462

If you are loading data using the LOAD DATA INFILE syntax, it should be possible to handle conversion on the fly.

Assuming that your source csv file looks like:

StartDate, EndDate, Value
"1/10/2012 10:05", "1/11/2012 11:51", abc
"1/13/2012 08:00", "1/15/2012 09:01", abc

You can defined columns StartDate and EndDate as datetime datatype, and simply do:

LOAD DATA INFILE '/path/to/my/file.csv' INTO TABLE mytable
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' -- or '\n'
    IGNORE 1 LINES
    (@StartDate, @EndDate, Value)
    SET 
        StartDate = STR_TO_DATE(@StartDate, '%c/%e/%Y %k:%i'),
        EndDate =   STR_TO_DATE(@EndDate,   '%c/%e/%Y %k:%i')
;

NB: and if you are curently not using LOAD DATA INFILE... I would still recommend migrating your code to use it. This is the proper way to do this in MySQL, it works on all clients.... and it is very fast (see this link).

Upvotes: 1

Nick
Nick

Reputation: 147166

Based on your sample data, the correct format string for STR_TO_DATE is

%c/%e/%Y %k:%i
  • %c allows for single digit month numbers
  • %e allows for single digit day numbers
  • %Y four digit year
  • %k allows for single digit hours
  • %i two digit minutes

All the format strings are described in the manual for DATE_FORMAT.

Demo on dbfiddle

Upvotes: 1

Related Questions