How to convert string to datetime type in mysql

Two conversion methods: 1. Use the str_to_date() function to format a string and convert it into a date and time value according to the specified format. The syntax is "str_to_date (string value, conversion format)". 2. Use the CAST() function to convert the specified string value to the datetime data type, the syntax is "CAST (string value AS datetime)".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL DATETIME type

The DATETIME type is used for values ​​that need to contain both date and time information, and requires 8 bytes for storage. The date format is 'YYYY-MM-DD HH:MM:SS', where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour, MM represents the minute, and SS represents the second.

MySQL method of converting string to datetime type

Method 1: Use str_to_date() function

str_to_date() is a specialized string to date function that can convert a string into a date and time value.

str_to_date(str, format)

• str: ​​Required. A string to be formatted as a date

• format: 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)

The STR_TO_DATE() function scans the input string to match the format string. The format string may contain literal characters and format specifiers starting with a percent (%) character.

Example:

SELECT STR_TO_DATE('21,5,2022','%d,%m,%Y');

SELECT STR_TO_DATE("2022,6,14 10,40,10", "%Y,%m,%d %h,%i,%s");

##Method 2: Use CAST() function

The CAST() function is used for type conversion to convert a value (of any type) to a specified data type.

Syntax for converting string to datetime type

CAST(字符串值 AS datetime)

Example:

SELECT CAST("2008.08.09 08:09:30" AS DATETIME);

[Related recommendations:

mysql video tutorial

】

The above is the detailed content of How to convert string to datetime type in mysql. For more information, please follow other related articles on the PHP Chinese website!