How to convert time with mysql verchar

In mysql, you can use the str_to_date function to convert verchar to a time type. The syntax of this function is "Str_to_date(col.pattern)", where the parameter col is the field or content that needs to be formatted.

Recommendation: "mysql video tutorial"

1.date type is changed to Varchar type

Use date_format function to convert, the usage method is

Date_format(col, pattern)

col is the field that needs to be formatted, pattern is the converted Format, refer to the general format table

The most commonly used format is xxxx-xx-xx xx:xx:xx. According to the format table, it needs to be written as:

%Y-%m- %d %H:%i:%s, that is:

DATE_FORMAT(xxx,'%Y-%m-%d %H:%i:%s')

2. Convert varchar type to date type

Use str_to_date function for conversion, the usage method is

Str_to_date(col.pattern), where col is the field that needs to be formatted or Content and pattern format refer to the format table. The usage is actually similar to converting date to varchar.

Attachment: Format table (translated)

##%r## Week ##%w #%UThe numbers represent the number of weeks, Sunday is the first day of the week## %DThe English suffix indicates the number of days in the month (1st, 2nd, 3rd...) %jmonth年

	Value	Meaning
Seconds	%S, %s	Two-digit seconds (00,01, ..., 59)
minutes	%i	Minute in two-digit form (00,01, ..., 59)
##hours	##%H	24-hour format, two-digit hours (00,01, ...,23)
	%h	12-hour format, two-digit hour (00,01, ...,12)
	%k	24-hour format, hours in numerical format (0,1, ...,23)
	##%l	## 12-hour format, hours in numerical form (0,1, ...,12)
	%T	24-hour format, time format (HH:mm:ss)
	## 12-hour format, time format ( hh:mm:ss AM or PM)
%p	AM morning or PM afternoon
%W	The name of each day of the week (Sunday, Monday, ..., Saturday)
## %a	##The abbreviation of the name of each day of the week (Sun, Mon, ..., Sat)
	#Identifies the week in numerical form (0=Sunday, 1=Monday, ..., 6=Saturday )
		##%u
The numbers represent the number of weeks, Monday is the first day of the week		sky	%d	Two digits represent the number of days in the month (01,02, ...,31)
	##%e	## The number represents the number of days in the month (1, 2, ..., 31)
	## With three The digit represents the number of days in the year (001,002, ...,366)
%M	English month name (January, February, ..., December)
%b	English abbreviation Month name (Jan, Feb, ..., Dec)
% m	Two digits represent the month (01, 02, ..., 12)
%c	The number represents the month (1,2, . ..,12)
##%Y	Four-digit year (2015, 2016...)		%y	Two-digit year (15,16...)
Text output	##%Text	Directly output text content

The above is the detailed content of How to convert time with mysql verchar. For more information, please follow other related articles on the PHP Chinese website!