Changing Date format in MySql
Question: I have a large Csv file that I wanna bulk-load it to my table. A column of
this csv file keeps the dates but in the DD-MM-YYYY format. When I am loading
this file to my table, it does not accept it, since MySQL DATE format only
accepts YYYY-MM-DD. Is there any way I could change this in MySQL. I am using
python to create my tables and to load my file into the table, which doesn't
matter for this problem actually.If there is a command for this, please tell
me where I should put it exactly? thanks This is the complete code

    
    
    import MySQLdb,os
    
    path='data_files'
    absPath = os.path.abspath(path)
    print absPath
    
    
    #connecting to the new database
    conn = MySQLdb.connect(host='localhost',
                              user='root',
                              passwd='',
                              db='iens')
    
    db_cursor = conn.cursor()
    
    query = "LOAD DATA LOCAL INFILE '"+ absPath + "/recencies" +"' INTO TABLE iens.recensies FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (restaurantID,Naam,Date,RecensieText) "
    
    db_cursor.execute(query)
    
    conn.commit()
    


Answer: Assuming your table has two columns named `column1` and `column2`, the date is
in `column2` and that is the second column in the CSV file:

    
    
    LOAD DATA INFILE 'filename' (column1, @var1)
    SET column2 = STR_TO_DATE(@var1, '%d-%m-%Y')
    

The `(column1, @var1)` part says:

  * The first column of the CSV file goes directly into the database column `column1`
  * The second column of the CSV file goes into a variable `@var1`

The `SET column2 =` part says:

  * Set the database column `column2` to whatever comes out of the expression following the `=` sign.

