Is there a way to make a date like "1980-1-12" a valid value for DATE type in MySQL?-CodePudding

I am trying to insert different dates into the table down below. The table

CREATE TABLE companies
(
    id INT AUTO_INCREMENT,
    name VARCHAR(45) NOT NULL,
    rate INT,
    created_on DATE NOT NULL,
    CHECK(rate >= 0 AND rate <= 10),
    PRIMARY KEY(id)
);

There are hundredths of different values, many of which are correct to the YYYY-MM-DD format and pass effortlessly. But there are also a handful of dates that have a format of YYYY-M-D or something similar. Is it possible to make a variable accept and correctly read both formats? Some of the values

  INSERT INTO companies(id, name, rate, created_on) VALUES 
    (1, 'Gaylord', 6, '2005-7-19'),
    (2, 'Feil Group', 9, '2018-1-26'),
    (3, 'Halvorson LLC', 3, '1988-1-24'),
    (4, 'Grant-Howell', 9, '1995-6-14'),
    (5, 'Gislason', 2, '2007-6-20'),
    (6, 'Harvey', 5, '2000-10-20'),
    (7, 'Kutch Ltd', 6, '2008-9-15'),
    (8, 'Harvey', 9, '2003-6-6'),
    (9, 'Wolff Inc', 5, '1989-4-8'),
    (10, 'Hackett PLC', 1, '1996-6-20'),
    (11, 'Schmeler', 3, '2010-7-26'),
    (12, 'Trantow-Breitenberg', 2, '1989-3-0')
);

The error message reads the following:

Error Code: 1292. Incorrect date value: '1989-3-0' for column 'created_on' at row 12

CodePudding user response：

All versions of mysql (at least back through 5.5 and likely earlier) will silently accept and correctly parse single digit month or year without error, even with STRICT_TRANS_TABLES enabled. You do not need to do anything special when you do your inserts.

If you want to see single digit month and year, do that using DATE_FORMAT(created_on,'%Y-%c-%e') when you select. If you find yourself doing that a lot, you could add a generated column:

alter table companies
    add formatted_created_on varchar(10) as (date_format(created_on,'%Y-%c-%e'))

and select formatted_created_on instead.