MySQL Date and Time Functions

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.