How Can I Easily Convert YYYYMMDD Dates to YYYY-MM-DD Using MySQL\'s LOAD DATA INFILE?

LOAD DATA INFILE: Converting YYYYMMDD to YYYY-MM-DD with Ease

In the process of data importing, encountering dates in the format of YYYYMMDD can be a common challenge. Fortunately, MySQL's LOAD DATA INFILE feature provides a convenient solution for such conversions.

To transform the dates in the specified format to the standard 'YYYY-MM-DD' format, employ the following syntax:

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:

• 'file.txt': Replace this placeholder with the actual file path.
• t1: Substitute this with the target table name.
• @var1: Denotes the temporary variable holding the YYYYMMDD date.
• column1, column3, ...: Adjust these to match your table's column names.
• column2: Represents the column receiving the converted date.
• STR_TO_DATE(@var1,'%Y%m%d'): The function transforming the date from YYYYMMDD to 'YYYY-MM-DD' format.

This command seamlessly loads data from the specified file and converts the 'YYYYMMDD' dates to the desired format in a single step, eliminating the need for external processing or manual conversions.

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