MySQL LOAD DATA Trick

I leaned a new trick today with LOAD DATA INFILE . I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY. So how do you load this into a Date Field.

`
$ echo “02-FEB-07” > /tmp/t1.psv
$ mysql -umysql
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(d1 DATE);

echo “02-FEB-07” > /tmp/t1.psv

LOAD DATA LOCAL INFILE ‘/tmp/t1.psv’
INTO TABLE t1 (@var1)
SET d1=STR_TO_DATE(@var1,’%d-%M-%y’);
SELECT * FROM t1;
EXIT
`

The trick is to bind the appropriate column within the file being loaded to a variable, @var1 in my example and use the SET syntax to perform a function on the variable. Rather cool.

A good tip to know.

Tagged with: Databases MySQL

Related Posts

Readyset QueryPilot Announcement

Readyset QueryPilot Announcement

At the MySQL and Heatwave Summit 2025 today, Readyset announced a new data systems architecture pattern named Readyset QueryPilot . This architecture which can front a MySQL or PostgreSQL database infrastructure, combines the enterprise-grade ProxySQL and Readyset caching with intelligent query monitoring and routing to help support applications scale and produce more predictable results with varied workloads.

Read more
More CPUs or Newer CPUs

More CPUs or Newer CPUs

In a CPU-bound database workload, regardless of price, would you scale-up or scale-new? What if price was the driving factor, would you scale-up or scale-new? I am using as a baseline the first available AWS Graviton2 processor for RDS (r6g).

Read more
An Interesting Artifact with AWS RDS Aurora Storage

An Interesting Artifact with AWS RDS Aurora Storage

As part of using public datasets with my own Benchmarking Suite I wanted upsize a dataset for larger volume testing. I have always used the INFORMATION_SCHEMA.TABLES data_length and index_length columns as a sufficiently accurate measurement for actual disk space used.

Read more