Implementation of comparison, sorting and other operations on varchar type dates in MySQL

When using MySQL, dates are generally stored in formats such as datetime and timestamp. However, sometimes due to special needs or historical reasons, the date is stored in varchar format. So how do we handle date data in this varchar format?

Use function: STR_TO_DATE(str, format)

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

Let’s take a case to illustrate this.

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

I created the following data in the table:

Now, if I need to select all members who registered in August 2018 and sort them by registration time, how can I do it? 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:

Summary: The STR_TO_DATE(str, format) function can convert varchar date data in the database into DATETIME date data. During the conversion process, please note that the format parameter must correspond to the format of str. After that, we can perform comparison, sorting and other operations!

This is the end of this article about the implementation of operations such as comparison and sorting of varchar type dates in MySQL. For more relevant mysql varchar type comparison and sorting content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!