Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to compare the size of the date in Mysql, I believe most people still do not know how to, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

If there is a table product with a field add_time, and its data type is datetime, someone might write sql like this:

code is as follows

select * from product where add_time = '2013-01-12'

For this statement, if you store the format YY-mm-dd is like this, then OK, if you store the format is: 2013-01-12 23:23:56 This format you are tragic, this is the part you can use DATE() function to return the date, so this sql should be processed as follows:

code is as follows

select * from product where Date(add_time) = '2013-01-12'

Another one, if you want to inquire about products added in January 2013?

code is as follows

select * from product where date(add_time) between '2013-01-01' and '2013-01-31'

You can also write:

select * from product where Year(add_time) = 2013 and Month(add_time) = 1

Do you know what mysql's date function does to your date comparison problem?

The value of date_col is within the last 30 days:

code is as follows

mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) select DAYOFWEEK('1998-02-03'); -> 3 WEEKDAY(date)

Returns the week index of date (0= Monday, 1= Tuesday, ……6= Sunday).

code is as follows

mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> select WEEKDAY('1997-11-05'); -> 2 DAYOFMONTH(date)

Returns the date of the month in the range 1 to 31.

code is as follows

mysql> select DAYOFMONTH('1998-02-03'); -> 3 DAYOFYEAR(date)

Returns the number of days in a year, in the range 1 to 366.

code is as follows

mysql> select DAYOFYEAR('1998-02-03'); -> 34 MONTH(date)

Returns the month of date, in the range 1 to 12.

code is as follows

mysql> select MONTH('1998-02-03'); -> 2 DAYNAME(date)

Returns the week name of date.

code is as follows

mysql> select DAYNAME("1998-02-05"); -> 'Thursday' MONTHNAME(date)

Returns the month name of date.

code is as follows

mysql> select MONTHNAME("1998-02-05"); -> 'February' QUARTER(date)

Returns the quarter of the year in the range 1 to 4.

code is as follows

mysql> select QUARTER ('98 -04- 01'); -> 2 The above is all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.