MySQL Tutorial/Date Time Functions/TIME FORMAT

TIME_FORMAT(time,format)

The TIME_FORMAT function can display a time in almost any format.

TIME_FORMAT() can only be passed time information, or it will return a NULL result.

TIME_FORMAT Output Formats

format Parameter Output Format %r 12-hour time (hh:mm:ss (AM|PM)) %T 24-hour time (hh:mm:ss) %Y Numeric year, 4 digits %y Numeric year, 2 digits %m Month with leading 0 (01, 02-12) %c Month without leading 0 (1, 2-12) %M Month name (January, February, and so on) %b Month name, abbreviated (Jan, Feb, and so on) %D Day of the month with an English suffix (1st, 2nd, and so on) %d Day of the month with leading 0 (00, 01, 02-31) %e Day of the month without leading 0 (0, 1, 2-31) %W Weekday name (Sunday, Monday, and so on) %a Weekday name, abbreviated (Sun, Mon, and so on) %H Hour (00, 01-23) %k Hour (0, 1-23) %h Hour (01, 02-12) %I Hour (01, 02-12) %l Hour (1, 2-12) %i Minutes (00, 01-59) %S Seconds (00, 01-59) %s Seconds (00, 01-59) %P AM or PM %U Week number in the year,in which Sunday is the first day of the week $u Week number in the year,in which Monday is the first day of the week %X & %V Year and week number, respectively,in which Sunday is the first day of the week %x & %v Year and week number, respectively,in which Monday is the first day of the week %j Day of year with leading 0"s (001, 002-366) %w Weekday number (0=Sunday, 1=Monday, and so on) %% Literal %

mysql>
mysql>
mysql> SELECT TIME_FORMAT(NOW(), "%H:%i:%s");
+--------------------------------+
| TIME_FORMAT(NOW(), "%H:%i:%s") |
+--------------------------------+
| 18:58:25                       |
+--------------------------------+
1 row in set (0.02 sec)
mysql>
mysql>

TIME_FORMAT(time,format): The format string may contain format specifiers only for hours, minutes, and seconds

mysql>
mysql> SELECT TIME_FORMAT("100:00:00", "%H %k %h %I %l");
+--------------------------------------------+
| TIME_FORMAT("100:00:00", "%H %k %h %I %l") |
+--------------------------------------------+
| 100 100 04 04 4                            |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>