Working with Dates and Times - Database Design Manual: Using MySQL for Windows - page 128

aDate is any object that contains a time part.
format is a string that contains the required parts of the date, using the following sym-
bols:
•

%H - the hour, in the 24-hour clock format, with a leading zero

•

%k - the hour, in the 24-hour clock format, without a leading zero

•

%l - the hour, between 1 and 12

•

%h - the hour, between 1 and 12, with a leading zero

•

%i - the minutes

•

%r - the full time, in the 12-hour clock (hh:mm:ss) with AM/PM shown

•

%T - the full time in the 24-hour clock (hh:mm:ss)

•

%S - the seconds

•

%p - AM or PM

So an alternative format for the CURRENT_TIME function would be:

TIME_FORMAT(NOW(),“The time is: %T”)

If you like the time to be output that way, but prefer to use hyphens instead of colons, you
could use:

TIME_FORMAT(NOW(),”My Time:%H-%i-%S”)

In the first example, we have just used the symbol %T, which has output one of the stan-
dard forms of the time format. Compare this with the second example, where, to get more
flexibility, we have used three symbols to refer to each part of the time specifically. We can
then easily insert any other characters (such as our hyphens) at any point in the format
string. We have also added other text within the format string, to give us a readable output
from the function. Figure 11.16 shows these functions working.

DATE_FORMAT
DATE_FORMAT() allows you to change the way that a given date and time is displayed. The
format of the statement is:

DATE_FORMAT(aDate,format)

Figure 11.16 Formatting the output of time.