What Is Data Type for Date in MySQL?
MySQL is a popular open-source relational database management system that supports various data types, including date and time. Storing dates and time accurately is crucial in many applications, from simple blog posts to complex financial systems.
In MySQL, you can use the DATE data type to store dates.
Date Data Type in MySQL
The DATE data type in MySQL allows you to store dates in the format ‘YYYY-MM-DD’. It represents a specific date without any time component.
For example, ‘2021-10-31’ represents October 31, 2021. The DATE data type can store dates ranging from ‘1000-01-01’ to ‘9999-12-31’.
Storing Dates Using the DATE Data Type
When creating a table in MySQL, you can specify the DATE data type for a column that will store dates. Let’s consider an example where we want to create a table named ’employees’ with a column named ‘birth_date’. We can define this column as follows:
CREATE TABLE employees ( id INT, name VARCHAR(50), birth_date DATE );
In the above example, the ‘birth_date’ column is defined as DATE. This means it can only store valid date values.
Inserting Dates into a Table
To insert dates into the table using SQL queries, you need to follow the correct format. The format should match the ‘YYYY-MM-DD’ pattern.
Here’s an example of how you can insert a date into the ‘birth_date’ column:
INSERT INTO employees (id, name, birth_date) VALUES (1, 'John Doe', '1990-05-15');
In the above example, we’re inserting May 15, 1990, as the birth date for the employee with ID 1 and name ‘John Doe’.
Retrieving Dates from a Table
When retrieving dates from a MySQL table, you can use various functions to format and manipulate them. One such function is DATE_FORMAT(), which allows you to convert the date into different formats.
Here’s an example of how you can retrieve the birth date in a different format:
SELECT id, name, DATE_FORMAT(birth_date,'%d %b %Y') AS formatted_birth_date FROM employees;
In the above example, we’re using the DATE_FORMAT() function to retrieve the birth date in the format ‘DD Mon YYYY’. This will give us results like ’15 May 1990′.
In MySQL, the DATE data type provides an efficient way to store dates without any time component. By using this data type correctly in your table definitions and SQL queries, you can ensure accurate storage and retrieval of dates in your MySQL database.