# In MySQL, how can we display time in other format specified by the user?

MySQLMySQLi Database

We can also use DATE_FORMAT() function to display the time in other formats. In this case, there would be two arguments of this function, first would be the time and second would be the format string.

The following example will change the current time in specified format −

mysql> SELECT DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p');

+-----------------------------------------------+
| DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p') |
+-----------------------------------------------+
| The time is 06:02:28 AM                       |
+-----------------------------------------------+
1 row in set (0.00 sec)

The following example will change the given time in specified format −

mysql> SELECT DATE_FORMAT('2017-10-29 06:03:25', 'The time is %h:%i:%s %p');

+---------------------------------------------------------------+
| DATE_FORMAT('2017-10-29 06:03:25', 'The time is %h:%i:%s %p') |
+---------------------------------------------------------------+
| The time is 06:03:25 AM                                       |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
Published on 19-Feb-2018 09:44:14