Search code examples
mysqlload-data-infilesqldatetime

Mysql read string as datetime

Hello I'm using LOAD DATA INFILE to populate a table in MySQL.

LOAD DATA INFILE 'test.txt'
INTO TABLE myTestTable
FIELDS TERMINATED BY '\t'
IGNORE 1 LINES;

Everything is working peachy except that there is a datetime column in my data that is formatted without any delimiter between the date and time sections. Like so:

SomeDateColumn
20050101081946

When I read this in, MySQL replaces all the dates with dummy values. Is there a way to have MySQL read this in correctly straight from a file?

Thanks!

Solution

  • You may call STR_TO_DATE when you run LOAD DATA, and convert the text date to a bona fide date on the fly:

    LOAD DATA INFILE 'test.txt'
INTO TABLE myTestTable
FIELDS TERMINATED BY '\t'
IGNORE 1 LINES
(
    col1, col2, @var1     -- list out all columns here
)

SET SomeDateColumn = STR_TO_DATE(@var1, '%Y%m%d%h%i%s');