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

Importing Dates from YYYYMMDD to YYYY-MM-DD Using LOAD DATA INFILE

When importing data from an INFILE using LOAD DATA INFILE, you may encounter dates stored in the YYYYMMDD format. This can be problematic when working with databases that require dates to be in the YYYY-MM-DD format.

Solution:

Fortunately, you can easily convert the dates to the desired format within the LOAD DATA INFILE statement itself, eliminating the need for external conversions using scripts. The STR_TO_DATE() function is used for this conversion.

For example, consider the following INFILE containing dates in the YYYYMMDD format:

AADR,20120403,31.43,31.43,31.4,31.4,1100
AAU,20120403,2.64,2.65,2.56,2.65,85700
AAVX,20120403,162.49,162.49,154.24,156.65,2200

To import this data and convert the dates to YYYY-MM-DD format in a single step, use the following LOAD DATA INFILE statement:

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 statement:

• @var1 is a placeholder for the YYYYMMDD date value in the INFILE.
• STR_TO_DATE() converts the @var1 value from the YYYYMMDD format to YYYY-MM-DD.
• %Y%m%d is the format specifier for the YYYYMMDD format.

After executing this statement, the dates in the t1 table will be in the YYYY-MM-DD format, making them compatible with database requirements and subsequent operations.

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