MySQL Date and Time Functions

The following describes the MySQL Date and Time Functions, weekly and date operations. If you need them, refer to them.

The following describes the MySQL Date and Time Functions, weekly and date operations. If you need them, refer to them.

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
> DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
-> 5
Mysql> select WEEKDAY ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the day of the week by the English name)
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month of the date value (returned by English name)
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of the year for date.
Mysql> select QUARTER ('98-04-01 ');
-> 2
WEEK (date, first)
Returns the week number Of The Year for date. (the default value of first is 0. The value of first is 1, indicating that Monday is the start of the week. 0 starts from Sunday)
Mysql> select WEEK ('2017-02-20 ');
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8
YEAR (date)
Returns the year of date (range: 1000 to 9999)
Mysql> select YEAR ('98-02-03 ');
-> 1998
HOUR (time)
Returns the hour of time (ranging from 0 to 23)
Mysql> select HOUR ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)
Mysql> select SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM)
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for P1 and P2)
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)
Addition and subtraction of date and time
(ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB (). You can also use operators and-instead of functions.