Take the 2-minute tour ×
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.

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

1 Answer 1

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)

mysql>

File:

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

Test:

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)

mysql>
share|improve this answer
    
Thanks a lot for your help Phil. –  Hitesh Chouhan May 9 '13 at 13:52
1  
@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

 
discard

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.