Automatically change date format when inserting in MySQL

Question

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

Look there: stackoverflow.com/questions/9426993/mysql-change-date-format — Kasyx
– Kasyx, Commented Mar 27, 2013 at 10:31
How do you intend to insert those entries? Which programming language are you using? — Mihai Matei
– Mihai Matei, Commented Mar 27, 2013 at 10:33

Jester · Accepted Answer · 2013-03-27 11:16:05Z

2

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)

edited Mar 27, 2013 at 11:16

answered Mar 27, 2013 at 10:39

Jester

1,3131 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

thenoirlatte Over a year ago

Thanks but why when i use it the result give NULL value.

Jester Over a year ago

It works for me:

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)

(Sorry, I can't properly format the output on a comment.

Jocelyn Over a year ago

@Jester don't comment, click "edit" on your answer to update it with more information.

Jester Over a year ago

@thenoirlatte If the solution worked for you, accept it as correct and upvote it please. ;)

thenoirlatte Over a year ago

i'm sorry, my reputation not enough to vote up, it needs 15 reputation :(

Collectives™ on Stack Overflow

Automatically change date format when inserting in MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related