Date

Overview

The DATE data type is used to store and insert date values.

The date value is stored without the time zone.

Structure

The date type contains three components: year, month, and day. It’s represented in a 32-bit integer. Here is the breakdown:

• Day component: 5 bits store the number of days within a month. Its value is in the range <1, 31>.
• Month component: 4 bits store the month of the year. Its value is in the range <1, 12>.
• Year component: 23 bits store the number of years. Its value is from range <0, 2^23 - 1>.

Format

YYYY-MM-DD

• YYYY - Four-digit year
• MM - One / two-digit month
• DD - One / two-digit day

Example

In this example, we will create an emp_submission table that consists of the candidate ID, candidate name, the submitted department, and a submission date with a DATE data type.

CREATE TABLE emp_submission (
    candidate_ID INT,
    candidate_Name TEXT,
    sub_dept TEXT,
    sub_date DATE
);

INSERT INTO emp_submission (candidate_ID, candidate_Name, sub_dept, sub_date)
VALUES 
(8557411, 'Kumar', 'HR', '2022-05-01'),
(8557421, 'Ricky', 'HR', '2022-01-09'),
(8557451, 'Alice', 'Finance', '2022-08-02'),
(8557461, 'Angel', 'Product', '2012-04-16'),
(8557431, 'Joan', 'Finance', '2022-02-02'),
(8557471, 'Cody', 'Product', '2022-03-20'),
(8557491, 'Liam', 'Product', '2022-06-15');

Now that the data has been inserted, let’s execute the SELECT statement below:

SELECT * FROM emp_submission;

The following is the result of the SELECT statement where the values in the sub_date column have DATE data type:

+---------------+------------------+------------+---------------+
| candidate_id  | candidate_name   | sub_dept   | sub_date      |
+---------------+------------------+------------+---------------+
| 8557411       | Kumar            | HR         | 2022-05-01    |
| 8557421       | Ricky            | HR         | 2022-01-09    |
| 8557451       | Alice            | Finance    | 2022-08-02    |
| 8557461       | Angel            | Product    | 2012-04-16    |
| 8557431       | Joan             | Finance    | 2022-02-02    |
| 8557471       | Cody             | Product    | 2022-03-20    |
| 8557491       | Liam             | Product    | 2022-06-15    |
+---------------+------------------+------------+---------------+