MYSQL format differs from Date_format function usage

One, FORMAT (x,d)

Format the number x to resemble the format ' #,###,###.## ', rounded to D for decimal. If D is 0, the result will not have a decimal point and fractional part.

Example:
1.

The code is as follows	Copy Code
SELECT FORMAT (12324.2573,3); Output: 12,324.257

2. Use when there is a where condition

The code is as follows	Copy Code
SELECT Book_name,format (book_price,4) From Book_mast WHERE book_price>150;

II, Time_format (Time,format)

This is used like the Date_format () function, but only for a few hours, the minutes and seconds format strings can contain format specifiers.

If the time value contains a portion of one hours, the value greater than the 23,%h and%k hour format specifier is greater than the normal range 0. 23. A small value generated by the one-hour format specifier (modulo 12).

code is as follows	copy code
mysql> SELECT time_format (' 100:00:00 ', '%H%k%H%I '%l '); +-------------- -------------------------------------------+ | Time_format (' 100:00:00 ', '%H%k%H%I%l ')                | +---------------------------------------------------------+ | 4     & nbsp;                                    | +---------------------------------------------------------+ 1 row in Set (0.00 sec)

Three, Date_format

Date_format () to format date or datetime values to get the desired format.
Format date values according to the format string:
%s,%s two-digit form of seconds (00, 01, ..., 59)
%i Two-digit form (00, 01, ..., 59)
%H two digits in the form of hours, 24 hours (00,01, ..., 23)
%h,%I Two digits in the form of hours, 12 hours (01,02, ..., 12)
%k digital form of hours, 24 hours (0,1, ..., 23)
%l digital form of hours, 12 hours (1, 2, ..., 12)
%T 24-hour time form (H h:m m:s s)
%r 12-hour time form (Hh:mm:ss AM or Hh:mm:ss PM)
%p AM or P M
%w the name of each day of the week (S u n d a y, Monday, ..., Saturday)
%a the name of each day of the week (Sun, Mon, ..., Sat)
%d two digits indicates the number of days in the month (00, 01, ..., 31)
%e numeric form represents days of the month (1, 2, ..., 31)
%d The English suffix indicates the number of days in the month (1st, 2nd, 3rd, ...). ）
%w represents the number of days in the week (0 = S u n d a y, 1=monday, ..., 6=saturday) www.111cn.net

%j a three-digit number of days in the year (001, 002, ..., 366)
% U Week (0, 1, 52), where Sunday is the first day of the week
%u weeks (0, 1, 52), where Monday is the first day of the week
%m Month name (J a n u a r y, February, ..., December)
%b Abbreviated month name (J a n u a r y, February, ..., December)
%m two digits of the month (01, 02, ..., 12)
The month (1, 2, ..., 12) that is represented by the%c number.
%Y four digits of year
%y two digits of year
Percent% direct value "%"

Summarize

Format

Hours, minutes and seconds format strings can contain format specifiers

Date_format

Returns a string of results based on formatting the date or date and time value date from the formatted string format

Time_format

Format the number x to resemble the format ' #,###,###.## ', rounded to D for decimal. If D is 0, the result will be no decimal point and fractional part