Menu

MySQL DATE Type: Range, Formats, and Invalid Dates

Learn MySQL DATE’s supported range, two-digit year rules, SQL mode behavior for invalid dates, and common date functions.

Updated on

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.

With ALLOW_INVALID_DATES disabled, MySQL checks whether the month and day form a valid calendar date. Strict SQL mode rejects an invalid date with an error; without strict mode, MySQL can convert it to 0000-00-00 and issue a warning. If ALLOW_INVALID_DATES is enabled, DATE and DATETIME values receive only a limited check: the month must be 1 through 12 and the day 1 through 31, so a value such as 2008-02-30 can be stored. This mode does not apply to TIMESTAMP. Zero dates and zero month/day parts have separate rules controlled by NO_ZERO_DATE, NO_ZERO_IN_DATE, and strict mode. Check the current session setting with SELECT @@SESSION.sql_mode; before relying on a particular outcome. See MySQL’s SQL mode documentation.

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 test_date 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');

With ALLOW_INVALID_DATES disabled and strict SQL mode enabled, MySQL rejects this insert with an incorrect-date error. If the mode is enabled, the same calendar-impossible date may be accepted because its month and day are within the permitted numeric ranges; without strict mode and without ALLOW_INVALID_DATES, MySQL may instead adjust it to the zero date and issue a warning.

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 the day of the month
  • MONTH(): return month
  • QUARTER(): return the quarter of the year
  • 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 is used to add days, weeks, months, years to a given date value:

SELECT CURDATE() AS today,
    DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS plus_1_day,
    DATE_ADD(CURDATE(), INTERVAL 1 WEEK) AS plus_1_week,
    DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AS plus_1_month,
    DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS plus_1_year;

+------------+------------+-------------+--------------+-------------+
| today      | plus_1_day | plus_1_week | plus_1_month | plus_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() AS today,
    DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS minus_1_day,
    DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AS minus_1_week,
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS minus_1_month,
    DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AS minus_1_year;

+------------+-------------+--------------+---------------+--------------+
| today      | minus_1_day | minus_1_week | minus_1_month | minus_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.