How to convert date to string in mysql

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:

##%mMonth name as numerical value ( 00 to 12) %pAM or PM %r Time is 12 Hour AM or PM format (hh:mm:ss AM/PM) %S Seconds (00 to 59) %sSeconds (00 to 59)%TTime in 24-hour format (hh:mm:ss)%USunday is the first day of the week (00 to 53) %uMonday is the first day of the week (00 to 53) %VSunday is the first day of the week (01 to 53) week. Used with %X%vMonday is the first day of the week (01 to 53). Use with %X%WWeekday Name All (Sunday to Saturday)%w Sunday = 0 and Saturday = 6 for the day of the week %XSunday is the first day of the week. Used with %V%xMonday is the first day of the week. Used with %V%YYear is a number, 4-digit value%yThe year is a number, a 2-digit value

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)

Example:

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 string

time_format(time,format)

format formatDescription%fMicroseconds (000000 to 999999)##%H%h% I%i%p%r%S%s%T

这用起来就像DATE_FORMAT()函数，但格式字符串格式说明包含只有几个小时，分钟和秒。

如果时间值包含小时的部分，其可大于23，在%H 和 %k小时格式说明符产生的值范围通常大于0..23。另小时格式说明符产生小时值为模12。

示例：

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

【相关推荐：mysql视频教程】

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!