Date Type

8min
This section discusses the Date data type and guides you on using and operating the date values.
OverviewThe DATE data type is used to store and insert date values.
💡 The date value is stored without the time zone.
StructureThe 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. It’s value is from range <0, 2^23 - 1>.
FormatFormat
|
YYYY-MM-DD
YYYY-MM-DD
﻿
YYYY - Four-digit year
DD - One / two-digit day
MM - One / two-digit month
ExampleIn this example, we will create an emp_submission table that consists of candidate ID, candidate name, the submitted department, and a submission date with a DATE data type.
Create a table
|
CREATE TABLE emp_submission (
candidate_ID int,
candidate_Name string,
sub_dept string,
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');
CREATE TABLE emp_submission (
candidate_ID int,
candidate_Name string,
sub_dept string,
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:
Display the table
|
SELECT * FROM emp_submission;
SELECT * FROM emp_submission;
﻿
The following is the result of the SELECT query where the values in the sub_date column using a DATE data type:
Output
|
+---------------+------------------+------------+---------------+
| 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    |
+---------------+------------------+------------+---------------+
+---------------+------------------+------------+---------------+
| 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    |
+---------------+------------------+------------+---------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
Boolean Type
Timestamp Type
Docs powered by archbee
TABLE OF CONTENTS