Load different date formats in mysql I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD. 

Is there a way by which I can load DD-MON-YY data into YYYY-MM-DD format using MySQL LOAD or any other native utility?
It's not difficult. Just need a set section in your LOAD DATA INFILE command.

mysql&gt; create table tst ( datecol datetime );
Query OK, 0 rows affected (0.04 sec)

mysql&gt;


File:

[root@node1 ~]# cat /tmp/tst.dat
01-JAN-03
21-MAR-09
28-FEB-11
[root@node1 ~]#


Test:

mysql&gt; load data infile '/tmp/tst.dat'
    -&gt; into table tst
    -&gt; fields terminated by '|'
    -&gt; lines terminated by '\n'
    -&gt; (@datecol)
    -&gt; set
    -&gt; datecol = str_to_date(@datecol, '%d-%M-%y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql&gt; select * from tst;
+---------------------+
| datecol             |
+---------------------+
| 2003-01-01 00:00:00 |
| 2009-03-21 00:00:00 |
| 2011-02-28 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql&gt;