Specifications

The format code %m represents the month as a 2-digit number; %d, the day as a 2-digit number;

and %Y, the year as a 4-digit number. A summary of the more useful MySQL format codes for

this purpose is shown in Table 18.3.

TABLE 18.3 Format Codes for MySQL’s DATE_FORMAT() Function

Code Description

%M Month, full text

%W Weekday name, full text

%D Day of month, numeric, with text suffix (for example, 1st)

%Y Year, numeric, 4-digits

%y Year, numeric, 2-digits

%a Weekday name, 3-characters

%d Day of month, numeric, leading zeroes

%e Day of month, numeric, no leading zeroes

%m Month, numeric, leading zeroes

%c Month, numeric, no leading zeroes

%b Month, text, 3-characters

%j Day of year, numeric

%H Hour, 24-hour clock, leading zeroes

%k Hour, 24-hour clock, no leading zeroes

%h or %I Hour, 12-hour clock, leading zeroes

%l Hour, 12-hour clock, no leading zeroes

%i Minutes, numeric, leading zeroes

%r Time, 12-hour (hh:mm:ss [AM|PM])

%T Time, 24-hour (hh:mm:ss)

%S or %s Seconds, numeric, leading zeroes

%p AM or PM

%w Day of the week, numeric, from 0 (Sunday) to 6 (Saturday)

The UNIX_TIMESTAMP function works similarly, but converts a column into a UNIX time stamp.

For example,

SELECT UNIX_TIMESTAMP(date_column)

FROM tablename;

Managing the Date and Time

HAPTER 18

MANAGING THE

DATE AND TIME

397

23 7842 CH18 3/6/01 3:43 PM Page 397