Question 88675 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL How To Upload CSV dates from 'mm/dd/yyyy' directly into a DATE data type format

I upload daily sales data into MySQL (Workbench) (AWS). Right now, our method of uploading regular dates like 5/10/2023 is to keep ANY AND ALL columns that include dates as a data type of VARCHAR. As a result I can’t use any DATE keywords/ranges, and we’ve had a number of typos that have had to be manually corrected.

I want to change all our tables to DATE data types, and my boss is a purist and wants the dates to directly load dates like 5/10/2023 format into MySQL dates converted directly to 2023-05-10. This seems like an easy task, but I cannot figure it out after several tests, mostly involving STR_TO_DATE and DATE_FORMAT functions. I feel like I’m close but a little off in my syntax somewhere

CSV Input File shows as date_col = 5/10/2023

LOAD DATA LOCAL INFILE 'C:/Users/LK/Desktop/Date Import Test.csv'
INTO TABLE Test_Table
    
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS

(date_col, blah_col1, blah_col2)
SET date_col = STR_TO_DATE(date_col,'%m/%d/%Y');

On that last line, I’ve also attempted this variation:

SET ld_new = DATE_FORMAT(STR_TO_DATE(ld_new, '%m/%d/%Y'), '%Y-%m-%d')

Everything ends up uploading as either NULL or 0000-00-00.

I want the result to be 2023-05-10, recognized by MySQL as a proper date data type.

Tags:

2

Answers


  1. 0

    You have to use a user variable in the column list.

    (@date_col, blah_col1, blah_col2)
SET date_col = STR_TO_DATE(@date_col,'%m/%d/%Y')
    Login or Signup to reply.
  2. 0

    NOTE: Before starting applying mentioned below, please create a backup of your tables and verify that all date values are in a consistent format. Here is the link how to do: backup

    1. First, you need to modify your existing tables to change the VARCHAR columns containing date values to the DATE data type. You can do this using the ALTER TABLE statement:
    ALTER TABLE your_table_name
MODIFY date_col DATE;
    1. Now, you can change LOAD DATA LOCAL INFILE query to import the CSV file:
    LOAD DATA LOCAL INFILE 'C:/Users/LK/Desktop/Date Import Test.csv'
INTO TABLE Test_Table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@date_col, blah_col1, blah_col2)
SET date_col = STR_TO_DATE(@date_col, '%m/%d/%Y');

    This query reads the data from your CSV file and maps the columns to the target table. The @date_col variable is used to temporarily store the date values from the CSV file, which are then converted to the correct DATE data type using the STR_TO_DATE() function with the format %m/%d/%Y.
    After the conversion, the date values should be stored in the MySQL table as a proper DATE data type, and by default, MySQL represents dates in the YYYY-MM-DD format.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search