What Is Date Data Type in MySQL?

In MySQL, the Date data type is used to store dates in the format ‘YYYY-MM-DD’. It is a commonly used data type for representing dates in various database systems. The date data type allows you to perform various operations on dates such as comparison, addition, subtraction, and formatting.

Working with Date Data Type

To work with the date data type in MySQL, you can perform various operations like:

1. Storing Dates

You can store dates in the date data type by specifying the value in the ‘YYYY-MM-DD’ format. For example:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    dob DATE
);

In the above example, we have created a table named ’employees’ with columns for employee id, name, and date of birth (dob). The dob column is defined as a date data type.

2. Retrieving Dates

To retrieve dates stored in the date data type column, you can use SQL SELECT queries. For example:

SELECT name, dob FROM employees;

This query will return the names and dates of birth of all employees from the ’employees’ table.

3. Comparing Dates

You can compare dates using comparison operators such as ‘<', '>‘, ‘<=', '>=’, ‘=’, or ‘<>‘. For example:

SELECT name FROM employees WHERE dob > '1990-01-01';

This query will return the names of employees whose date of birth is after January 1, 1990.

4. Performing Date Calculations

You can perform calculations on dates using various date functions provided by MySQL. For example, you can add or subtract days, months, or years from a date. Here’s an example:

SELECT DATE_ADD(dob, INTERVAL 1 YEAR) AS next_birthday FROM employees;

This query will return the next birthday of each employee by adding 1 year to their date of birth.

5. Formatting Dates

You can format dates in different ways using the DATE_FORMAT function in MySQL. For example:

SELECT name, DATE_FORMAT(dob, '%d-%m-%Y') AS formatted_dob FROM employees;

This query will return the names and formatted dates of birth of all employees in the ‘dd-mm-yyyy’ format.

Conclusion

The date data type in MySQL is a powerful tool for storing and manipulating dates. It allows you to perform various operations on dates and format them according to your requirements.

• Storing Dates: Use the ‘YYYY-MM-DD’ format to store dates.
• Retrieving Dates: Use SQL SELECT queries to retrieve dates.
• Comparing Dates: Use comparison operators to compare dates.
• Performing Date Calculations: Utilize date functions for calculations on dates.
• Formatting Dates: Format dates using the DATE_FORMAT function.

By understanding and utilizing the date data type in MySQL effectively, you can handle various date-related operations efficiently in your database applications.