Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql varchar type how to achieve date comparison, sorting and other operations, the article is very detailed, has a certain reference value, interested friends must read it!

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

Use function: STR_TO_DATE (str, format)

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

The following is an example to illustrate.

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

I created the following pieces of data in the table:

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

SELECT id,nickname,createtime FROM memberWHERE str_to_date (createtime,'% Ymuri% mmi% d') BETWEEN str_to_date ('2018-08-01,'% Ymure% mmi% d') AND str_to_date ('2018-08-31,'% Ymuri% mmi% d') ORDER BY str_to_date (createtime,'% YMel% mMel% d')

The implementation results are as follows:

These are all the contents of this article entitled "how to compare and sort dates with varchar types in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.