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

Security GenAI

MITRE ATLAS - A 2025 Review

The MITRE ATLAS Matrix provides a working knowledge base of adversary tactics, techniques, and real-world case studies specifically for Artificial Intelligence (AI) and Machine Learning (ML) systems. It adapts the established MITRE ATT&CK adversary tactics and techniques matrix which is the standard reference for traditional IT cybersecurity.

Read more
Foundational Frameworks for GenAI Security Analysis

Foundational Frameworks for GenAI Security Analysis

Three frameworks form the analytical foundation of this series’ threat analysis, selected for their complementary coverage of the GenAI security landscape and their broad adoption across enterprise security practices. The MITRE Adversarial Threat Landscape for AI Systems (MITRE ATLAS) maps tactics and techniques specific to AI systems.

Read more
A first look at MySQL 26.7 Early Access

A first look at MySQL 26.7 Early Access

MySQL has dropped its newest release , categorized as “Early Access” and available at https://labs.mysql.com/ . While this post is not going to go into depth, I wanted to at least validate the management changes you verify between normal MySQL upgrades.

Read more