MySQL in the date comparison size method detailed

If a table product has a field add_time and its data type is datetime, someone might write SQL like this:

The code is as follows	Copy Code
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 you can use the date () function to return the part of the date, So this SQL should be handled as follows:

The code is as follows	Copy Code
SELECT * FROM product where Date (add_time) = ' 2013-01-12 '

One more, if you want to inquire about the products that were added in January 2013?

The code is as follows	Copy Code
SELECT * FROM product where date (add_time) between ' 2013-01-01 ' and ' 2013-01-31 ' You can also write this: SELECT * FROM product where year (add_time) = 2013 and Month (add_time) = 1

Do you know what the MySQL date function is doing to your date comparison problem?

The value of its date_col is within the last 30 days:

The code is as follows	Copy Code
Mysql> SELECT something from table WHERE To_days (now ())-To_days (Date_col) <= 30;

DayOfWeek (date)
Returns the week index of date (1= Sunday, 2 = Monday, ...). 7= Saturday). These index values correspond to ODBC standards.

The code is as follows	Copy Code
Mysql> Select DayOfWeek (' 1998-02-03 '); -> 3

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

The code is as follows	Copy Code
Mysql> Select Weekday (' 1997-10-04 22:23:00 '); -> 5 Mysql> Select Weekday (' 1997-11-05 '); -> 2

DayOfMonth (date)
Returns the date in the month of date, in the range 1 through 31.

The code is as follows	Copy Code
Mysql> Select DayOfMonth (' 1998-02-03 '); -> 3

DayOfYear (date)
Returns the number of days in a year, in the range of 1 to 366.

The code is as follows	Copy Code
Mysql> Select DayOfYear (' 1998-02-03 '); -> 34

MONTH (date)
Returns the month of date, ranging from 1 to 12.

The code is as follows	Copy Code
Mysql> Select MONTH (' 1998-02-03 '); -> 2

Dayname (date)
Returns the name of the week of date.

The code is as follows	Copy Code
Mysql> Select Dayname ("1998-02-05"); -> ' Thursday '

MonthName (date)
Returns the month name of the date.

The code is as follows	Copy Code
Mysql> Select MonthName ("1998-02-05"); -> ' February '

Quarter (date)
Returns the quarter of date one year, ranging from 1 to 4.

The code is as follows	Copy Code
Mysql> Select quarter (' 98-04-01 '); -> 2