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

aDate is any date object or string that can be converted into a date.
format is a string that contains the required parts of the date, using all of the previously
mentioned time symbols with the addition of the following:

•

%M - full text of month name

•

%W - day of the week, written in full

•

%D - day of the month with added text (i.e. 1st, 2nd, 3rd)

•

%Y - the year, using 4 digits

•

%y - the year, using 2 digits

•

%a - abbreviated day of the week

•

%d - day of the month with leading zero

•

%e - standard day of the month

•

%m - month number with leading zero

•

%c - standard month number

•

%b - month name abbreviated to three characters

•

%j - day of year

•

%w - day of the week in numbers, starting with Sunday = 0

We can demonstrate most of these in one query as follows:

SELECT CURRENT_DATE AS currentdate,

DATE_FORMAT( NOW(),
“%W, %D %M, %Y. DayNumber: %j. %a %d-%m-%y”)

I could have included time parts in that query as well, but then the results may have not
fitted in Figure 11.17. The query has used different date parts, punctuation spacing and
text, to format the date in our chosen way. Although it is quite hard to remember all of the
symbols for formatting dates and time - and there are more described in the on-line man-
ual - one way is as follows. D , M and Y are easily remembered as Day, Month and Year. If
they are written in capitals then it is the full un-abbreviated version of the date part. If you
write them in lower case, d , m and y , you get the shortened format for the part. Don't forget
to include the percent sign (%) in front of any of the symbols.

Figure 11.17 Formatting the date function.