Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

It's not difficult. Just need a set section in your LOAD DATA INFILE command.

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



[root@node1 ~]# cat /tmp/tst.dat
[root@node1 ~]#


mysql> load data infile '/tmp/tst.dat'
    -> into table tst
    -> fields terminated by '|'
    -> lines terminated by '\n'
    -> (@datecol)
    -> set
    -> 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> 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)

share|improve this answer
Thanks a lot for your help Phil. – Hitesh Chouhan May 9 '13 at 13:52
@user2118349 That's ok. If you found the answer helpful, please mark it as accepted by clicking the "tick" at the top-left. – Phil May 9 '13 at 14:00
done. thanks again. – Hitesh Chouhan May 10 '13 at 13:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.