The MySQL DATE data type is used to store dates in the format ‘YYYY-MM-DD.’ It represents a calendar date without any time component. MySQL provides several date-related data types, including DATE, TIME, DATETIME, and TIMESTAMP, each serving different purposes. In this explanation, we’ll focus specifically on the DATE data type.

Syntax

Here are some key points about the MySQL DATE data type:

Format

The DATE data type stores dates in the format ‘YYYY-MM-DD,’ where:

YYYY represents the four-digit year.
MM represents the two-digit month (01 for January through 12 for December).
DD represents the two-digit day of the month (01 through 31).

Range

The DATE data type in MySQL supports a range of dates from ‘1000-01-01’ to ‘9999-12-31.’ This provides a wide span for storing historical and future dates.

Storage Size

The storage size for the DATE data type is 3 bytes, which is relatively compact compared to other date and time data types.

Default Value

If you define a column with the DATE data type and do not provide a default value, the default is ‘0000-00-00.’ However, it’s advisable to set a more meaningful default value or allow NULL if appropriate.

Date Functions

MySQL provides various functions for manipulating and extracting information from DATE values. Some common functions include:

CURDATE: Returns the current date.
NOW: Returns the current date and time.
DATE_ADD: Adds a specified time interval to a date.
DATE_SUB: Subtracts a specified time interval from a date.
DATEDIFF: Returns the difference in days between two dates.

Example

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATE
);

INSERT INTO events (event_id, event_name, event_date)
VALUES
    (1, 'Birthday Party', '2023-03-15'),
    (2, 'Conference', '2023-06-20'),
    (3, 'Anniversary', '2023-09-05');

In this example, a table named ‘events’ is created with a column of the DATE data type to store event dates. The table is then populated with sample data.

Comparisons

You can perform various comparisons with DATE values, such as checking for equality, finding dates before or after a certain point, and determining the difference between two dates.

SELECT * 
FROM events 
WHERE event_date > '2023-06-01';

This query retrieves events that occur after June 1, 2023.

In summary, the MySQL DATE data type is a fundamental component for storing and manipulating dates in a database. Its simplicity and efficiency make it suitable for scenarios where a full timestamp is not required, focusing solely on calendar dates.