How to Convert YYYYMMDD Dates to YYYY-MM-DD During MySQL LOAD DATA INFILE?

Loading Data with YYYYMMDD to YYYY-MM-DD Conversion Using LOAD DATA INFILE

When importing data using LOAD DATA INFILE, it can be a hassle to encounter dates in the YYYYMMDD format. However, MySQL offers a solution to effortlessly convert these dates to the more common YYYY-MM-DD format during the loading process.

To achieve this conversion, utilize the STR_TO_DATE() function within the SET clause of the LOAD DATA INFILE statement. Below is an example demonstrating this technique:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
FIELDS TERMINATED BY ',' 
(column1, @var1, column3, ...)
SET column2 = STR_TO_DATE(@var1,'%Y%m%d')

In this example, the @var1 variable temporarily stores the YYYYMMDD formatted date value, which is then converted to the YYYY-MM-DD format by STR_TO_DATE(). By using this method, you can load data and convert dates in a single step, eliminating the need for external scripts or intermediate processing.

The above is the detailed content of How to Convert YYYYMMDD Dates to YYYY-MM-DD During MySQL LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!