mysqlsqlstr-to-date

Mysql Select statement to and convert timestamp on the fly

I have a MYSQL table which has a column name called timestamp stored in string format. the timestamps in this column are in the following format e.g. '20/10/2014 05:39 PM'

Now how can select a row and convert the timestamp column to 24HR format on the fly.

basically I want something like this. SELECT id, user, STR_TO_DATE(timestamp, '%d/%m/%Y %h:%i %p') as timestamp FROM mytable WHERE user="bob";

but this does not work. looks like its not recognizing the timestamp variable inside STR_TO_DATE sql function and its retuning NULL for the timestamp column.

Please help.

Solution

  • Looks fine to me:

    mysql> SELECT STR_TO_DATE('20/10/2014 05:39 PM', '%d/%m/%Y %h:%i %p');
+---------------------------------------------------------+
| STR_TO_DATE('20/10/2014 05:39 PM', '%d/%m/%Y %h:%i %p') |
+---------------------------------------------------------+
| 2014-10-20 17:39:00                                     |
+---------------------------------------------------------+

    (I know this should be a Comment, but is an Answer in order to get formatting.)