لطفا منتظر باشید ...
Date and Time Functions
DAYOFWEEK(date) | This function returns the weekday of date in the form of an integer, according to the ODBC standard (1 = Sunday, 2 = Monday, 3 = Tuesday, … 7 = Saturday). | |
WEEKDAY(date) | This function returns the weekday of date in the form of an integer (0 = Monday, 1 = Tuesday, 2 = Wednesday, … 6 = Sunday). | |
DAYOFMONTH(date) | This function returns the day of the month for date (from 1 to 31). | |
DAYOFYEAR(date) | This function returns the day of the year for date (from 1 to 366—remember leap years!). | |
MONTH(date) | This function returns the month for date (from 1, January, to 12, December). | |
DAYNAME(date) | Returns the name of the day of the week for date (e.g. 'Tuesday'). | |
MONTHNAME(date) | This function returns the name of the month for date (e.g. 'April'). | |
QUARTER(date) | This function returns the quarter of the year for date (e.g.: QUARTER('2001-04-12') = 2). | |
WEEK(date,first),WEEK(date) | This function returns the week of the year for date (from 1 to 53), assuming by default that the first day of the week is Sunday (if first is not specified or 0), or Monday (if first is 1). | |
YEAR(date) | Returns the year for date (from 1000 to 9999). | |
YEARWEEK(date),YEARWEEK(date,first) | This function returns the year and week for date in the form YYYYWW. Note that the first or last day or two of the year may often belong to a week of the year before or after, respectively.E.g.: YEARWEEK("2001-12-31") = 200201 | |
HOUR(time) | This function returns the hour for time (from 0 to 23). | |
MINUTE(time) | This function returns the minute for time (from 0 to 59). | |
SECOND(time) | This function returns the second for time (from 0 to 59). | |
PERIOD_ADD(period,num_months) | This function adds num_months months to period (specified as YYMM or YYYYMM) and returns the value in the form YYYYMM. | |
PERIOD_DIFF(period1,period2) | This function returns the number of months between period1 and period2 (each of which should be specified as YYMM or YYYYMM). | |
DATE_ADD(date, INTERVAL expr type) ,DATE_SUB(date, INTERVAL expr type), ADDDATE(date, INTERVAL expr type), SUBDATE(date, INTERVAL expr type) | This function returns the result of either adding or subtracting the specified interval of time to or from date (a DATE or DATETIME value). DATE_ADD and ADDDATE are identical, as are DATE_SUB and SUBDATE. expr specifies the interval to be added or subtracted and may be negative if you wish to specify a negative interval, and type specifies the format of expr, as shown in "Interval types for date addition/subtraction functions". |
If date and expr involve only
date values, the result will be a DATE value; otherwise,
this function will return a DATETIME value.
| type | Format for expr |
|---|---|
| SECOND | number of seconds |
| MINUTE | number of minutes |
| HOUR | number of hours |
| DAY | number of days |
| MONTH | number of months |
| YEAR | number of 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' |
Here are a few examples to help you see how this family of functions
works:The following both return the date six months from now:
ADDDATE(CURDATE(), INTERVAL 6 MONTH)
DATE_ADD(CURDATE(), INTERVAL '0-6' YEAR_MONTH)
The following all return this time tomorrow:
ADDDATE(NOW(), INTERVAL 1 DAY)
SUBDATE(NOW(), INTERVAL -1 DAY)
DATE_ADD(NOW(), INTERVAL '24:0:0' HOUR_SECOND)
DATE_ADD(NOW(), INTERVAL '1 0:0' DAY_MINUTE)
TO_DAYS(date) | This function converts date to a number of days since year 0. Allows you to calculate differences in dates (i.e. TO_DAYS(date1) - TO_DAYS(date2) = days_in_between). | |
FROM_DAYS(days) | Given the number of days since year 0 (as produced by TO_DAYS), this function returns a date. | |
DATE_FORMAT(date,format) | This function takes the date or time value date and returns it formatted according to the formatting string format, which may contain any of the symbols shown in "DATE_FORMAT symbols (2004-01-01 01:00:00)" as place-holders. |
| Symbol | Displays | Example |
|---|---|---|
| %M | Month name | January |
| %W | Weekday name | Thursday |
| %D | Day of the month with English suffix | 1st |
| %Y | Year, numeric, 4 digits | 2004 |
| %y | Year, numeric, 2 digits | 03 |
| %a | Abbreviated weekday name | Thu |
| %d | Day of the month | 01 |
| %e | Day of the month | 1 |
| %m | Month of the year, numeric | 01 |
| %c | Month of the year, numeric | 1 |
| %b | Abbreviated month name | Jan |
| %j | Day of the year | 001 |
| %H | Hour of the day (24 hour format, 00-23) | 01 |
| %k | Hour of the day (24 hour format, 0-23) | 1 |
| %h | Hour of the day (12 hour format, 01-12) | 01 |
| %I | Hour of the day (12 hour format, 01-12) | 01 |
| %l | Hour of the day (12 hour format, 1-12) | 1 |
| %i | Minutes | 00 |
| %r | Time, 12 hour (hh:mm:ss AM/PM) | 01:00:00 AM |
| %T | Time, 24 hour (hh:mm:ss) | 01:00:00 |
| %S | Seconds | 00 |
| %s | Seconds | 00 |
| %p | AM or PM | AM |
| %w | Day of the week, numeric (0=Sunday) | 4 |
| %U | Week (00-53), Sunday 1st day of the week | 00 |
| %u | Week (00-53), Monday 1st day of the week | 01 |
| %X | Year of the week where Sunday is the 1st day of the week, 4 digits (use with %V) | 2003 |
| %V | Week (01-53), Sunday 1st day of week (%X) | 53 |
| %x | Like %X, Monday 1st day of week (use with %v) | 2004 |
| %v | Week (01-53), Monday 1st day of week (%x) | 01 |
| %% | An actual percent sign | % |
TIME_FORMAT(time,format) | This function is the same as DATE_FORMAT, except the format string may only contain symbols referring to hours, minutes, and seconds. | |
CURDATE(),CURRENT_DATE | This function returns the current system date in the SQL date format 'YYYY-MM-DD' (if used as a date) or as YYYYMMDD (if used as a number). | |
CURTIME(),CURRENT_TIME | This function returns the current system time in the SQL time format 'HH:MM:SS' (if used as a time) or as HHMMSS (if used as a number). | |
NOW(),SYSDATE(),CURRENT_TIMESTAMP | This function returns the current system date and time in SQL date/time format 'YYYY-MM-DD HH:MM:SS' (if used as a date/time) or as YYYYMMDDHHMMSS (if used as a number). | |
UNIX_TIMESTAMP(),UNIX_TIMESTAMP(date) | This function returns either the current system date and time, or the specified date/time as the number of seconds since 1970-01-01 00:00:00 GMT. | |
FROM_UNIXTIME(unix_timestamp) | The opposite of UNIX_TIMESTAMP, this function converts a number of seconds from 1970-01-01 00:00:00 GMT to "YYYY-MM-DD HH:MM:SS" (if used as a date/time) or YYYYMMDDHHMMSS (if used as a number), local time. | |
FROM_UNIXTIME(unix_timestamp,format) | This function formats a UNIX timestamp according to the format string, which may contain any of the symbols listed in "DATE_FORMAT symbols (2004-01-01 01:00:00)". | |
SEC_TO_TIME(seconds) | This function converts some number of seconds to the format 'HH:MM:SS' (if used as a time) or HHMMSS (if used as a number). | |
TIME_TO_SEC(time) | This function converts a time in the format 'HH:MM:SS' to a number of seconds. |
