Mysql has two ways to convert dates to strings: 1. Use the date_format() function to format the date value according to the specified format and return the formatted date string. The syntax "date_format(specified Date, specified format)". 2. Use the time_format() function to convert the specified date and time into a string, the syntax is "time_format (specified date and time, specified format)".
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Two methods for mysql to convert date and time into string
1. Use the date_format() function
The date_format() function can format date values according to the specified format and return the formatted date string
DATE_FORMAT(date, format)
date Required. The date to be formatted
format is required. The format to use. Can be one or a combination of the following values:
format format | description |
---|---|
%a | Abbreviated name of working day (Sunday to Saturday) |
%b | Abbreviated name of month (January to December) |
%C | Numeric month name (0 to 12) |
%D | Month as numeric value A certain day in , followed by the suffix (1st, 2nd, 3rd,...) |
%d | as the month date of the value (01 to 31) |
%e | a day of the month as a value (0 to 31) |
%f | Microseconds (000000 to 999999) |
%H | Hours (00 to 23) |
%h | Hours (00 to 12) |
%I | Hours (00 to 12) |
%i | Minutes (00 to 59) |
%j | Day of the Year (001 to 366) |
%k | Hours (0 to 23) |
%l | Hours (1 to 12) |
%M | Month name (January to December) |
Month name as numerical value ( 00 to 12) | |
AM or PM | |
Time is 12 Hour AM or PM format (hh:mm:ss AM/PM) | |
Seconds (00 to 59) | |
Seconds (00 to 59) | |
Time in 24-hour format (hh:mm:ss) | |
Sunday is the first day of the week (00 to 53) | |
Monday is the first day of the week (00 to 53) | |
Sunday is the first day of the week (01 to 53) week. Used with %X | |
Monday is the first day of the week (01 to 53). Use with %X | |
Weekday Name All (Sunday to Saturday) | |
Sunday = 0 and Saturday = 6 for the day of the week | |
Sunday is the first day of the week. Used with %V | |
Monday is the first day of the week. Used with %V | |
Year is a number, 4-digit value | |
The year is a number, a 2-digit value |
SELECT DATE_FORMAT("2022-06-15", "%Y %m %d");
SELECT DATE_FORMAT("2022-06-15", "%M %d %Y");
2. Use the time_format() function
time_format() function to convert the specified date and time into a stringtime_format(time,format)
这用起来就像DATE_FORMAT()函数,但格式字符串格式说明包含只有几个小时,分钟和秒。
如果时间值包含小时的部分,其可大于23,在%H 和 %k小时格式说明符产生的值范围通常大于0..23。另小时格式说明符产生小时值为模12。
示例:
SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
【相关推荐:mysql视频教程】
Description | |
---|---|
Microseconds (000000 to 999999) | |
Hours (00 to 23) | |
Hours (00 to 12) | |
hours (00 to 12) | |
minutes (00 to 59) | |
AM or PM | |
12 hour time in AM or PM format (hh:mm:ss AM/PM) | |
seconds (00 to 59) | |
seconds (00 to 59) | |
24-hour time (hh:mm:ss) |
The above is the detailed content of How to convert date to string in mysql. For more information, please follow other related articles on the PHP Chinese website!