Home > Database > Mysql Tutorial > body text

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

王林
Release: 2023-05-31 13:49:19
forward
2210 people have browsed it

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:

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

I created the following pieces of data in the table:

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

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')
Copy after login

The execution results are as follows:

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

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!

Related labels:
source:yisu.com
Statement of this Website
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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template