How to implement date comparison, sorting and other operations with the varchar type in mysql

During the use of mysql, dates are generally stored in datetime, timestamp and other formats, but sometimes due to special needs or historical reasons, the date storage format is varchar, so how should we deal with this varchar format? What about date data?

Use function: STR_TO_DATE(str, format)

The inverse function of DATE_FORMAT() function is STR_TO_DATE(str, format) function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains date and time components, or a date or time value if the string contains only date or time components.

The following is a case to illustrate.

For example, I now have a membership table. The structure of the table is as follows:

I created the following pieces of data in the table:

Now, if I need to select all members registered in August 2018 and sort them by registration time, what should I do? Here is a reference:

SELECT id,nickname,createtime 
FROM member
WHERE str_to_date(createtime, '%Y-%m-%d') BETWEEN str_to_date('2018-08-01', '%Y-%m-%d') AND str_to_date('2018-08-31', '%Y-%m-%d')
ORDER BY str_to_date(createtime, '%Y-%m-%d')

The execution results are as follows:

The above is the detailed content of How to implement date comparison, sorting and other operations with the varchar type in mysql. For more information, please follow other related articles on the PHP Chinese website!