Home>Article>Database> How to convert date to string in mysql

How to convert date to string in mysql

青灯夜游
青灯夜游 Original
2022-06-15 19:29:10 90409browse

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)
  • date Required. The date to be formatted

  • format is required. The format to use. Can be one or a combination of the following values:

##%m Month name as numerical value ( 00 to 12) %p AM or PM %r Time is 12 Hour AM or PM format (hh:mm:ss AM/PM) %S Seconds (00 to 59) %s Seconds (00 to 59) %T Time in 24-hour format (hh:mm:ss) %U Sunday is the first day of the week (00 to 53) %u Monday is the first day of the week (00 to 53) %V Sunday is the first day of the week (01 to 53) week. Used with %X %v Monday is the first day of the week (01 to 53). Use with %X %W Weekday Name All (Sunday to Saturday) %w Sunday = 0 and Saturday = 6 for the day of the week %X Sunday is the first day of the week. Used with %V %x Monday is the first day of the week. Used with %V %Y Year is a number, 4-digit value %y The 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");

How to convert date to string in mysql

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

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)

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

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

示例:

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

How to convert date to string in mysql

【相关推荐:mysql视频教程

format format Description %f Microseconds (000000 to 999999) ##%H %h % I %i %p %r %S %s %T
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!

Statement:
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