CODEWITHSUNDEEP
mysqlcsv
thenoirlatte
thenoirlatte

Reputation: 349

Automatically change date format when inserting in MySQL

I try to insert an csv file into mysql table. And one of the column in the file have date format like this: 3/15/2013 17:41:38 . Is it possible to insert the column, and change the date format become like this: 2013-03-15 17:41:38 ?

Thanks before

Upvotes: 1

Views: 971

Answers (1)

Jester
Jester

Reputation: 1303

You can use STR_TO_DATE for your insert, so you can put:

STR_TO_DATE( '3/15/2013 17:41:38', '%c/%e/%Y %H:%i:%s' )

as your date column.

The format reffers to the format of the input string, and the output format will be the standard MySQL date format as you need.

The output should be as:

mysql> select STR_TO_DATE( '3/15/2013 17:41:38', '%c/%e/%Y %H:%i:%s' );
+----------------------------------------------------------+
| STR_TO_DATE( '3/15/2013 17:41:38', '%c/%e/%Y %H:%i:%s' ) |
+----------------------------------------------------------+
| 2013-03-15 17:41:38                                      |
+----------------------------------------------------------+
1 row in set (0.08 sec)

Upvotes: 2

Related Questions