MySQL Date comparison function

The following query selects all records and its date_col value 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

Example 1

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:

The code is as follows	Copy Code
SELECT * FROM product where year (add_time) = 2013 and Month (add_time) = 1

Share some MySQL common date functions

Now () returns the current date and time
Curdate () returns the current date
Curtime () returns the current time
Date () extracts dates or date parts of date/time expressions
EXTRACT () returns a separate part of the date/time
Date_add () Adds a specified time interval to a date
Date_sub () subtracting the specified time interval from the date
DATEDIFF () returns the number of days between two dates
Date_format () Displays the date/time in a different format