MySQL string type conversion time type _ MySQL

MySQL string type conversion time type bitsCN.com

If the MySQL database uses the varchar (or char) type at a certain time, it is convenient for the system to use it, instead of converting the time type to adapt to different database versions, to convert the retrieved field to a time type, perform the following operations:

(1) str_to_date

This function can fully translate the string time.

The SQL statement can be written as follows:

SELECT str_to_date ('tablename'. 'eventtime', '% Y-% m-% d % H: % I: % S') from tablename

In the database, the eventTime type is VARCHAR (20), so that you can replace the type with the time type.

(2) to_days

Just like its name, it can only be converted to every day, that is, the time string of a day is converted into a number.

To:

The string type is converted to the time type. the string value must meet the 17:08:00 mode.

SELECT STR_TO_DATE ('2014/1/04/31: 2: 26', '% m/% d/% Y, % H: % I: % S'); success.

Note: The mode is case sensitive.
SELECT STR_TO_DATE ('1970-10-19, 17: 08 ',' % Y-% m-% d, % H: % I '); failed.
SELECT STR_TO_DATE ('1970-10-19,17: 08', '% Y-% m-% d, % H: % I'); after all spaces are removed, the operation succeeds.
SELECT STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I') from upload; the conversion result is displayed in advance. As expected.
The official conversion is as follows:
Update upload set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Update uzer set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Update hlog set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Convert the column definition to the datetime type.
Note that mysql is only accurate to seconds.

Note:
% W name of the week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H Hour (00 ...... 23)
% K hour (0 ...... 23)
% H Hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM
% W days in a week (0 = Sunday ...... 6 = Saturday)
% U week (0 ...... 52). Sunday is the first day of the week.
% U week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

BitsCN.com