Using the Date Type in MySQL

The DATE type takes in a string value and converts it into a YYYY-MM-DD date format.

Let's say we have a SQL schema that uses the DATE type, and that inserts one row.

CREATE TABLE IF NOT EXISTS DailySales(
  date_id DATE,
  make_name VARCHAR(20),
  lead_id INT,
  partner_id INT
);
TRUNCATE TABLE DailySales;
INSERT INTO DailySales (date_id, make_name, lead_id, partner_id)
VALUES ('2020-12-8', 'toyota', '0', '1');

This will give us the following table.

DailySales

date_id	make_name	lead_id	partner_id
2020-12-18	toyota	0	1

If we tried to insert this value as some kind of number, we would get an ER_TRUNCATED_WRONG_VALUE error.

INSERT INTO DailySales (date_id, make_name, lead_id, partner_id)
VALUES (0, 'toyota', '0', '1');

If we tried to insert this value as a non-dashed number, we would also get an ER_TRUNCATED_WRONG_VALUE error.

INSERT INTO DailySales (date_id, make_name, lead_id, partner_id)
VALUES ('0', 'toyota', '0', '1');

In order for the date type to work, we need to give it three realistic numbers, each of which are separated by a dash -.

For instance, 1-2-3 is considered a realistic number. This is the third day of the second month of the first year.

1000-02-25 is also considered a realistic number. This is the 25th day of the second month of the 1000th year.

2000-02-30 is not considered a realistic number and will throw an error. This is because there will never be a 30th day in February.

When we do give MySQL a realistic number, say 1-2-3, it will automatically format the number to YYYY-MM-DD.

INSERT INTO DailySales (date_id, make_name, lead_id, partner_id)
VALUES ('1-2-3', 'toyota', '0', '1');

date_id	make_name	lead_id	partner_id
0001-02-03	toyota	0	1

rpivo/index.md

Using the Date Type in MySQL

DailySales