Introduction to MySQL DATE Data Type

In MySQL, DATE is a date data type used for storing year, month, and day values. Its value range is from ‘1000-01-01’ to ‘9999-12-31’. Using the DATE data type makes it easy to perform date calculations and formatting.

Syntax

The syntax for the DATE data type is as follows:

DATE

Use Cases

The DATE data type is typically used for storing date values, such as birthdates, transaction dates, etc.

Examples

Here are two examples of using the DATE data type:

Example 1

Create a students table with a birthdate column for storing students’ birthdates.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE
);

INSERT INTO students (id, name, birthdate)
VALUES
    (1, 'Alice', '2000-01-01'),
    (2, 'Bob', '1999-05-10'),
    (3, 'Charlie', '1998-11-20');

Query the students table, ordering by birthdate in ascending order.

SELECT * FROM students ORDER BY birthdate ASC;

Output:

+----+---------+------------+
| id | name    | birthdate  |
+----+---------+------------+
| 3  | Charlie | 1998-11-20 |
| 2  | Bob     | 1999-05-10 |
| 1  | Alice   | 2000-01-01 |
+----+---------+------------+

Example 2

Create an orders table with an order_date column for storing order dates.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    order_date DATE
);

INSERT INTO orders (id, customer_name, order_date)
VALUES
    (1, 'Alice', '2022-01-01'),
    (2, 'Bob', '2022-02-10'),
    (3, 'Charlie', '2022-03-20');

Query the orders table, finding all orders from the year 2022.

SELECT * FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';