How to convert date to string in mysql-Mysql Tutorial-php.cn

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)".

How to convert date to string in mysql

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)

Copy after login

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");

Copy after login

How to convert date to string in mysql

SELECT DATE_FORMAT("2022-06-15", "%M %d %Y");

Copy after login

How to convert date to string in mysql

2. Use the time_format() function

time_format() function to convert the specified date and time into a string

time_format(time,format)

Copy after login

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(&#39;100:00:00&#39;, &#39;%H %k %h %I %l&#39;);

Copy after login

How to convert date to string in mysql

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

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!

Related labels：

mysql

source：php.cn

Previous article：How to add comments to columns (fields) in mysql Next article：How to modify the content of a field in mysql

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn



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)