Mysql> SELECT something from Tbl_name
WHERE To_days (now ())-To_days (Date_col) <= 30;
DayOfWeek (date)
Returns the week index of date (1 = Sunday, 2 = Monday, ...). 7 = Saturday). Index values are compliant with ODBC standards.
mysql> SELECT DayOfWeek (' 1998-02-03 ');
-> 3
Weekday (date)
Returns the week index of date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday):
Mysql> SELECT Weekday (' 1998-02-03 22:23:00 ');
-> 1
Mysql> SELECT Weekday (' 1997-11-05 ');
-> 2
DayOfMonth (date)
Returns date is the first day of January, ranging from 1 to 31:
mysql> SELECT dayofmonth (' 1998-02-03 ');
-> 3
DayOfYear (date)
Returns date is the first day of the year, ranging from 1 to 366:
mysql> SELECT dayofyear (' 1998-02-03 ');
-> 34
MONTH (date)
Returns the month in date, ranging from 1 to 12:
mysql> SELECT MONTH (' 1998-02-03 ');
-> 2
Dayname (date)
Returns the week name of date:
mysql> SELECT dayname ("1998-02-05");
-> ' Thursday '
MonthName (date)
Returns the month name of date:
mysql> SELECT monthname ("1998-02-05");
-> ' February '
Quarter (date)
Returns date in the quarter of the year, with a range of 1 to 4:
Mysql> SELECT Quarter (' 98-04-01 ');
-> 2
WEEK (date)
WEEK (Date,first)
For the Sunday is the first day of the week, if the function has only one parameter call, return date is the week ordinal of the year, the return value range is 0 to 53 (yes, there may be the beginning of the 53rd week). The two-parameter WEEK () allows you to specify whether a week starts in Sunday or Monday and returns a value of 0-53 or 1-52. A table here shows how the second argument works: value meaning
01 weeks starting from Sunday, the return value range is 0-53
1 week starting in Monday, the return value range is 0-53
2 week starting in Sunday, the return value range is 1-53
31 weeks starting from Monday, the return value range is 1-53 (ISO 8601)
mysql> SELECT WEEK (' 1998-02-20 ');
-> 7
mysql> SELECT WEEK (' 1998-02-20 ', 0);
-> 7
mysql> SELECT WEEK (' 1998-02-20 ', 1);
-> 8
mysql> SELECT WEEK (' 1998-12-31 ', 1);
-> 53
Note that in version 4.0, WEEK (#,0) was changed to match the USA calendar. Note that if the week is the last week of the previous year, MySQL will return 0 if you do not use 2 or 3 as optional parameters:
mysql> SELECT year (' 2000-01-01 '), WEEK (' 2000-01-01 ', 0);
-> 2000, 0
mysql> SELECT WEEK (' 2000-01-01 ', 2);
-> 52
You might argue that the MySQL WEEK () function should return 52 when the given date value is actually part of the 52nd week of 1999. We decided to return 0 because we wanted the function to return "weeks in the specified year". This makes the use of the WEEK () function reliable when used in conjunction with the function of the month-day value in its extracted date value. If you prefer to get the appropriate year-week value, you should use parameter 2 or 3 as an optional parameter, or use the function Yearweek ():
mysql> SELECT yearweek (' 2000-01-01 ');
-> 199952
Mysql> SELECT MID (Yearweek (' 2000-01-01 '), 5,2);
-> 52
Year (date)
Returns the year of date with a range of 1000 to 9999:
mysql> SELECT year (' 98-02-03 ');
-> 1998
Yearweek (date)
Yearweek (Date,first)
Returns a date value which is the week of the year. The form and function of the second parameter are exactly the same as the second parameter of WEEK (). Note that for a given date parameter is the first or last week of the year, the returned year value may be inconsistent with the year given by the date parameter:
mysql> SELECT yearweek (' 1987-01-01 ');
-> 198653
Note that for optional parameters 0 or 1, the return value of the week value differs from the value returned by the WEEK () function (0), and the WEEK () returns the week value according to the given year context.
HOUR (Time)
Returns the time value of time, ranging from 0 to 23:
mysql> SELECT HOUR (' 10:05:03 ');
-> 10
MINUTE (Time)
Returns the minute value of time, ranging from 0 to 59:
mysql> SELECT MINUTE (' 98-02-03 10:05:03 ');
-> 5
SECOND (Time)
Returns the second value of time, ranging from 0 to 59:
mysql> SELECT SECOND (' 10:05:03 ');
-> 3
Period_add (P,n)
Add N months to period P (format yymm or yyyymm). Returns a value in YYYYMM format. Note that the period parameter P is not a date value:
Mysql> SELECT Period_add (9801,2);
-> 199803
Period_diff (P1,P2)
Returns the number of months between P1 and P2. P1 and P2 should be specified in Yymm or yyyymm. Note that the period parameter P1 and P2 are not date values:
Mysql> SELECT Period_diff (9802,199703);
-> 11
Date_add (Date,interval expr type)
Date_sub (Date,interval expr type)
Adddate (Date,interval expr type)
Subdate (Date,interval expr type)
These functions perform arithmetic operations on dates. Adddate () and subdate () are synonyms for Date_add () and Date_sub () respectively. In MySQL 3.23, if the right side of the expression is a date value or a date-time field, you can use + and-instead of Date_add () and Date_sub () (see below for example). Parameter date is a DATETIME or date value that specifies the beginning of a date. Expr is an expression that specifies whether to increase or subtract the interval value from the start date. expr is a string; it can represent a negative interval value with a "-" lead. Type is a keyword that indicates that an expression is interpreted in Hoge form. The following table shows how the type and expr parameters are associated: Type value expr expected format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
Day
MONTH MONTHS
Year YEARS
Minute_second "Minutes:seconds"
Hour_minute "Hours:minutes"
Day_hour "Days HOURS"
Year_month "Years-months"
Hour_second "HOURS:MINUTES:SECONDS"
Day_minute "Days Hours:minutes"
Day_second "Days HOURS:MINUTES:SECONDS"
In the format of expr, MySQL allows any character as a delimiter. The suggested bounding characters are shown in the table. If the date parameter is a date value and the calculated interval has only the year, MONTH, and day portions (no time part), then the return value is also a date value. Otherwise, the return value is a DATETIME value:
mysql> Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 day + "1997-12-31";
-> 1998-01-01
mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT date_add ("1997-12-31 23:59:59",
-> INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT date_add ("1997-12-31 23:59:59",
-> INTERVAL 1 day);
-> 1998-01-01 23:59:59
mysql> SELECT date_add ("1997-12-31 23:59:59",
-> INTERVAL "1:1" minute_second);
-> 1998-01-01 00:01:00
mysql> SELECT date_sub ("1998-01-01 00:00:00",
-> INTERVAL "1 1:1:1" day_second);
-> 1997-12-30 22:58:59
mysql> SELECT date_add ("1998-01-01 00:00:00",
-> INTERVAL "-1" day_hour);
-> 1997-12-30 14:00:00
mysql> SELECT date_sub ("1998-01-02", INTERVAL);
-> 1997-12-02
Current 1/2 page
12 Next read the full text
