Introduction to MySQL DATE

In this article, we’ll introduce MySQL DATE data type and show you some useful date functions to work with date data efficiently.

There are five data types in MySQL for managing dates and times, including: DATE, TIME, DATETIME, TIMESTAMP and YEAR. This article focuses on the DATE data type.

MySQL uses the yyyy-mm-dd format to store date values. If you want to display in another date format, for example mm-dd-yyyy, you can use the DATE_FORMAT function to format the date to your desired format.

MySQL DATE type values ​​range from 1000-01-01to 9999-12-31.

When you insert values ​​into DATE columns, you can use string values in yyyy-mm-dd or yy-mm-dd format.

In strict mode, you cannot insert invalid dates like: 2018-08-32. MySQL will give an error. In non-strict mode, MySQL converts invalid dates to 0000-00-00.

MySQL DATE syntax

The following is the syntax for definiting a DATE column :

column_name DATE;

Here, we don’t need to specify any attributes for DATE.

MySQL Date Example

We said above that you can insert date values ​​in the yyyy-mm-dd or yy-mm-dd format into the DATE column.

If you use a two-digit year value, MySQL still converts it to a four-digit year value according to the following rules:

  • Year values ​​in the range 00-69 will be converted to 2000-2069.
  • Year values ​​in the range 70-99 will be converted to 1970-1999.

While the rules are clear, two-digit year values ​​can confuse people reading your code, so you should avoid to use two-digit years.

Let’s look at the example below.

First, create a table test_date that contains the column created_date of DATE data type.

CREATE TABLE test_date (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_date DATE NOT NULL
);

Next, insert a row into the test_date table.

INSERT INTO test_date(created_date)
VALUES ('2008-08-08');

Then, query the data from the test_date table.

SELECT * FROM test_date;

+----+--------------+
| id | created_date |
+----+--------------+
|  1 | 2008-08-08   |
+----+--------------+

After that, insert two rows with two-digit years into the people table.

INSERT INTO test_date(created_date)
VALUES ('09-09-09'),  ('99-09-09');

Here, according to the conversion rules, the year 09 will be converted to 2009, 99 will be converted to 1999.

Finally, we can query the data from the test_date table to check if the data was transformed according to the conversion rules.

SELECT * FROM test_date;

+----+--------------+
| id | created_date |
+----+--------------+
|  1 | 2008-08-08   |
|  2 | 2009-09-09   |
|  3 | 1999-09-09   |
+----+--------------+

Let’s try another statement that inserts a row with a wrong year value:

INSERT INTO test_date(created_date)
VALUES ('2008-02-30');

Here MySQL will return an error: ERROR 1292 (22007): Incorrect date value: ‘2008-02-30’ for column ‘created_date’ at row 1.

MySQL DATE function

MySQL provides many useful date functions that allow you to manipulate dates efficiently:

  • NOW(): get the current date and time
  • CURDATE(): get the current date
  • DATE(): get the date part
  • DATE_FORMAT(): formatted output date
  • DATEDIFF(): Calculate the number of days between two dates
  • DATE_ADD(): Increment the given time interval on the given date
  • DATE_SUB(): decrement the given time interval on the given date
  • DAY(): return date midday
  • MONTH(): return month
  • QUARTER(): return to season
  • YEAR(): return year
  • WEEK(): The function returns the week of the year that the given date is
  • WEEKDAY(): The function returns the weekday index
  • WEEKOFYEAR(): The function returns the calendar week

NOW()

The NOW() function is used to get the current date and time. See the following statement:

SELECT NOW();

+---------------------+
| NOW()               |
+---------------------+
| 2022-03-25 06:47:58 |
+---------------------+

CURDATE()

The CURDATE() function is used to get the current system date. See the following statement:

SELECT CURDATE();

+------------+
| CURDATE()  |
+------------+
| 2022-03-25 |
+------------+

DATE()

The DATE() function is used to return the date part from a date or datetime value. See the following statement:

SELECT DATE(NOW());

+-------------+
| DATE(NOW()) |
+-------------+
| 2022-03-25  |
+-------------+

DATE_FORMAT()

To format date values, use the DATE_FORMAT() function. The following statement returns a date in %m/%d/%Y format:

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;

+------------+
| today      |
+------------+
| 03/25/2022 |
+------------+

DATEDIFF()

To calculate the number of days between two date values, use the DATEDIFF() function as follows:

SELECT DATEDIFF('2021-01-01','2022-01-01') days;

+------+
| days |
+------+
| -365 |
+------+

Here, it returns a negative number if the first argument is earlier than the second, otherwise returns a positive number or 0 otherwise.

DATE_ADD()

The DATE_ADD() function are used to add days, weeks, months, years to a given date value:

SELECT CURDATE() `今天`,
    DATE_ADD(CURDATE(), INTERVAL 1 DAY) '一天后',
    DATE_ADD(CURDATE(), INTERVAL 1 WEEK) '一周后',
    DATE_ADD(CURDATE(), INTERVAL 1 MONTH) '一月后',
    DATE_ADD(CURDATE(), INTERVAL 1 YEAR) '一年后';

+------------+------------+------------+------------+------------+
| 今天       | 一天后     | 一周后     | 一月后     | 一年后     |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-26 | 2022-04-01 | 2022-04-25 | 2023-03-25 |
+------------+------------+------------+------------+------------+

DATE_SUB()

The DATE_SUB() function is used to subtract an interval from a date:

SELECT CURDATE() `今天`,
    DATE_SUB(CURDATE(), INTERVAL 1 DAY) '一天前',
    DATE_SUB(CURDATE(), INTERVAL 1 WEEK) '一周前',
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) '一月前',
    DATE_SUB(CURDATE(), INTERVAL 1 YEAR) '一年前';

+------------+------------+------------+------------+------------+
| 今天       | 一天前     | 一周前     | 一月前     | 一年前     |
+------------+------------+------------+------------+------------+
| 2022-03-25 | 2022-03-24 | 2022-03-18 | 2022-02-25 | 2021-03-25 |
+------------+------------+------------+------------+------------+

DAY, MONTH, QUARTER, YEAR

If you want to get the day, month, quarter and year of the date value, you can use the corresponding DAY(), MONTH(), QUARTER(), and YEAR() functions as follows:

SELECT DAY(CURDATE()) `day`,
    MONTH(CURDATE()) `month`,
    QUARTER(CURDATE()) `quarter`,
    YEAR(CURDATE()) `year`;

+------+-------+---------+------+
| day  | month | quarter | year |
+------+-------+---------+------+
|   25 |     3 |       1 | 2022 |
+------+-------+---------+------+

WEEK, WEEKDAY, WEEKOFYEAR

The WEEK function returns the week number, the WEEKDAY function returns the weekday index, and the WEEKOFYEAR function returns the calendar week.

SELECT WEEKDAY(CURDATE()) `weekday`,
    WEEK(CURDATE()) `week`,
    WEEKOFYEAR(CURDATE()) `weekofyear`;

+---------+------+------------+
| weekday | week | weekofyear |
+---------+------+------------+
|       4 |   12 |         12 |
+---------+------+------------+

Conclusion

In this tutorial, you learned about MySQL DATE data type and how to use some useful date functions to manipulate date values.

  • The storage format of the MySQL DATE value is yyyy-mm-dd.
  • You can insert date values ​​using the yyyy-mm-dd or yy-mm-dd format.