MySQL DATE type and DATE functions

1. DATE type in MySQL

DATE type is one of five temporary datatypes for date and time values, along with TIME, DATETIME, TIMESTAMP, and YEAR.

DATE represents a date value with no time part. MySQL displays DATE values in 'YYYY-MM-DD' format with the supported range from '1000-01-01' to '9999-12-31'. MySQL might recognize DATE values in these formats:

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ”relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2019-10-04', '2019/10/04', '2019^10^04', and '2019@10@04' are equivalent. 
As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'. 
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Keep in mind that despite multiple formats accepted, the DATE value must be presented in year-month-day order.

For DATE values with two digit year part, MySQL interprets dates specified with ambiguous year values using these rules:

Year values in the range 00-69 are converted to 2000-2069.
Year values in the range 70-99 are converted to 1970-1999.

2. DATE functions

Here are some commonly used functions to work with DATE values in MySQL

DATE() function to get the date value from a date time value:

SELECT DATE('2019-10-04 01:20:30');

The result is “2019-10-04”

CURDATE() function to return the current date as a value in ’YYYY-MM-DD’ or YYYYMMDD format

SELECT CURDATE();

The result is “2019-10-04”

DATEDIFF() to return the difference as a value in days between two date values:

SELECT DATEDIFF('2019-10-04','2018-12-30');

The result is 278

DATE_FORMAT() function to format the date value according to the format string.

SELECT DATE_FORMAT('2019-10-04 22:23:00', '%D %M %Y');

The result is “4th October 2019”

DATE_ADD() function to add a number of days, weeks, months, years, etc., to a date value:

SELECT 
    DATE_ADD('2019-10-04', INTERVAL 10 DAY) AS '10 days later',
    DATE_ADD('2019-10-04', INTERVAL 10 WEEK) AS '10 weeks later',
    DATE_ADD('2019-10-04', INTERVAL 10 MONTH) AS '10 months later',
    DATE_ADD('2019-10-04', INTERVAL 10 YEAR) AS '10 years later';

The result is

10 days later	10 weeks later	10 months later	10 years later
2019-10-14	2019-12-13	2020-08-04	2029-10-04

DAYOFMONTH(), or DAY() function, is to return the day on month for a date value:

SELECT DAYOFMONTH('2019-10-04');

The result is “4”

DAYNAME() function is to return the name of the weekday for a date value:

SELECT DAYNAME('2019-10-04');

The result is “Friday”

DAYOFWEEK() function is to return the day of the week for a date value as the week starts with Sunday (Sunday = 1):

SELECT DAYOFYEAR('2019-10-04');

The result is 6

DAYOFYEAR() function is to return the day of the year for a date value:

SELECT DAYOFYEAR('2019-10-04');

The result is 277

EXTRACT() function is to extract parts from the date value

SELECT
	EXTRACT(YEAR FROM '2019-10-04') AS y, 
	EXTRACT(MONTH FROM '2019-10-04') AS m, 
	EXTRACT(DAY FROM '2019-10-04') AS d,
	EXTRACT(YEAR_MONTH FROM '2019-10-04') AS ym;

The result is:

y	m	d	ym
2019	10	4	201910

LAST_DAY() function is to return the last day of the month from the input date value:

SELECT LAST_DAY('2019-10-04');

The result is “2019-10-31”

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.