title: Load CSV file to MySQL
body: <p>I am trying to load csv file into MySQL database using below command. But getting error for date column as below. Can someone help me with this?</p>
  <pre><code>LOAD DATA INFILE '/var/lib/mysql-files/ES_USER_ROLES.csv'
  INTO TABLE ES_USER_ROLES
  FIELDS TERMINATED BY ','
  ENCLOSED BY '&quot;' 
  LINES TERMINATED BY '\n'
  (ID,EMP_ID,FULL_NAME,ROLE_ID,STATUS,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
  SET CREATION_DATE = DATE_FORMAT(STR_TO_DATE(@CREATION_DATE,'%d-%b-%y'),'%Y-%m-%d');
  </code></pre>
  <p>Snippet from CSV file :</p>
  <pre><code>root@ctrls-ux-pacman-uat-sql:~# head -2 /var/lib/mysql-files/ES_USER_ROLES.csv
  25153,&quot;108918&quot;,&quot;&quot;,68,,118299,09-JUN-21,118299,09-JUN-21
  3256,&quot;200363&quot;,&quot;&quot;,69,,517209,16-AUG-18,517209,16-AUG-18
  </code></pre>
  <p>MySQL error:</p>
  <pre><code>mysql&gt; LOAD DATA INFILE '/var/lib/mysql-files/ES_USER_ROLES.csv'
      -&gt; INTO TABLE ES_USER_ROLES
      -&gt; FIELDS TERMINATED BY ','
      -&gt; ENCLOSED BY '&quot;'
      -&gt; LINES TERMINATED BY '\n'
      -&gt; (ID,EMP_ID,FULL_NAME,ROLE_ID,STATUS,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
      -&gt; SET CREATION_DATE = DATE_FORMAT(STR_TO_DATE(@CREATION_DATE,'%d-%b-%y'),'%Y-%m-%d');
  ERROR 1292 (22007): Incorrect date value: '09-JUN-21' for column 'CREATION_DATE' at row 1
  
  mysql&gt; desc ES_USER_ROLES;
  +------------------+--------------+------+-----+---------+----------------+
  | Field            | Type         | Null | Key | Default | Extra          |
  +------------------+--------------+------+-----+---------+----------------+
  | ID               | int          | NO   | PRI | NULL    | auto_increment |
  | EMP_ID           | varchar(200) | YES  |     | NULL    |                |
  | FULL_NAME        | varchar(200) | YES  |     | NULL    |                |
  | ROLE_ID          | int          | YES  |     | NULL    |                |
  | STATUS           | int          | YES  |     | NULL    |                |
  | CREATED_BY       | int          | YES  |     | NULL    |                |
  | CREATION_DATE    | date         | YES  |     | NULL    |                |
  | LAST_UPDATED_BY  | int          | YES  |     | NULL    |                |
  | LAST_UPDATE_DATE | date         | YES  |     | NULL    |                |
  +------------------+--------------+------+-----+---------+----------------+
  9 rows in set (0.00 sec)
  </code></pre>