MySQL DATE Data Type

Introduction

In this chapter, we will learn about the DATE data type in MySQL. The DATE data type is used to store date values without time information. It is useful for applications that require tracking dates, such as birthdates, event dates, or any other date-related data. We will cover the syntax for defining DATE columns, examples, and important considerations for using DATE in MySQL.

Syntax

The basic syntax for defining a DATE column in MySQL is:

DATE

The DATE data type stores date values in the format YYYY-MM-DD.

Example

Here is an example of how to define a DATE column in a table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthdate DATE
);

In this example, birthdate is defined as a DATE column to store the birthdates of employees.

Full Example

Let’s go through a complete example where we create a database and a table with DATE columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with DATE Columns:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthdate DATE,
    hire_date DATE
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, birthdate, hire_date) VALUES
('Rahul', 'Sharma', '1985-05-15', '2010-07-01'),
('Priya', 'Singh', '1990-03-22', '2015-06-15'),
('Amit', 'Kumar', '1987-09-30', '2012-11-20'),
('Neha', 'Verma', '1992-12-05', '2018-04-10'),
('Sahil', 'Mehta', '1980-08-25', '2008-01-15');

5. Query the Employees Table:

SELECT * FROM employees;

Output

In this example, the birthdate and hire_date columns are defined as DATE to store the birthdates and hire dates of employees.

Using DATE Values in Queries

You can use DATE values in queries to filter, sort, and manipulate date information.

Example: Query Employees Hired After a Specific Date

SELECT * FROM employees WHERE hire_date > '2012-01-01';

Output

Example: Query Employees Born Before a Specific Date

SELECT * FROM employees WHERE birthdate < '1990-01-01';

Output

Example: Query Employees with a Specific Birthdate

SELECT * FROM employees WHERE birthdate = '1990-03-22';

Output

Important Considerations

• Storage Requirements: The DATE data type uses 3 bytes of storage.

• Range: The DATE data type supports a range from '1000-01-01' to '9999-12-31'.

• Format: The DATE data type follows the YYYY-MM-DD format, which is consistent with the ISO 8601 standard.

• Default Values: You can set default values for DATE columns, such as the current date using the CURDATE() function.

  CREATE TABLE logs (
      id INT PRIMARY KEY AUTO_INCREMENT,
      message VARCHAR(255),
      log_date DATE DEFAULT CURDATE()
  );
  

Conclusion

The DATE data type is essential for storing date information in MySQL. This chapter covered the syntax for defining DATE columns, provided a full example with different date attributes, and discussed important considerations.