Differences between MySql format and date_format Functions

There are several formatting functions in mysql, such as format, date_format, and TIME_FORMAT. Many of them do not understand them. I will give you a detailed example to illustrate the usage of these three functions.

1. FORMAT (X, D)

Format the number X in a format similar to '#,###,######'. Rounding to D is a decimal number. If D is 0, no decimal point or decimal point is returned.

Example:
1.

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

2. Use the where clause

The Code is as follows:	Copy code
SELECT book_name, FORMAT (book_price, 4) FROM book_mast WHERE book_price> 150;

2. TIME_FORMAT (time, format)

This is the same as the DATE_FORMAT () function, but it only takes a few hours. The minute and second format strings can contain format specifiers.

If the time value contains part of an hour, a value greater than 23, % H, and % k hour format specifiers generate a value greater than the normal range of 0 .. 23. The Hour value generated by the one-hour format specifier (Modulo 12 ).

The Code is as follows:	Copy code
Mysql> SELECT TIME_FORMAT ('2014: 00: 00', '% H % k % h % I % l '); + ----------------------------------------------------------- + | TIME_FORMAT ('2014: 00: 00', '% H % k % h % I % l') | + ----------------------------------------------------------- + | 100 100 04 4 | + ----------------------------------------------------------- + 1 row in set (0.00 sec)

3. date_format

DATE_FORMAT () to format the DATE or DATETIME value to obtain the expected format.
Format the date value based on the format string:
% S, % s two-digit second (,..., 59)
% I two-digit score (,..., 59)
% H two-digit hour, 24 hours (,..., 23)
% H, % I two-digit hour, 12 hours (,..., 12)
% K, 24 hours (0, 1,..., 23)
% L hour in numeric format (1, 2,..., 12)
% T 24-hour time format (h: m: s)
% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)
% P AM or P M
% W name of each day in a week (S u n d a y, Monday,..., Saturday)
% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)
% D indicates the number of days in a month (00, 01,..., 31)
% E indicates the number of days in a month (1, 2,..., 31)
% D indicates the number of days in a month (1st, 2nd, 3rd ,...)
% W represents the number of days in a week (0 = S u n d a y, 1 = Monday,..., 6 = Saturday) www. bKjia. c0m

% J represents the number of days in a year in three digits (001,002,..., 366)
% U Week (0, 1, 52), where Sunday is the first day of the week
% U Week (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 double-digit month (01, 02,..., 12)
% C indicates the month (1, 2,..., 12)
The year in the format of % Y.
The year in double-digit % y format.
% Direct value "%"

Summary

Format

Hour, minute, and second format strings can contain format specifiers

Date_format

Format the date or date and Time Value Based on the format string to return the result string.

TIME_FORMAT

Format the number X in a format similar to '#,###,######'. Rounding to D is a decimal number. If D is 0, no decimal point or decimal point is returned.