Date Type
Overview
The DATE data type is used to store and insert date values.
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 |
+---------------+------------------+------------+---------------+