SQL DML: Incorrect date value (MySQL)

Question

I created a table in my database:

CREATE TABLE official_receipt(
    student_no INT UNSIGNED,
    academic_year CHAR(8),
    trimester ENUM('1', '2', '3'),
    or_no MEDIUMINT UNSIGNED,
    issue_date DATE NOT NULL,
    received_from VARCHAR(255) NOT NULL,
    amount_of DECIMAL(8,2) NOT NULL,
    issued_by VARCHAR(255),
    doc_type ENUM('FULL', 'DOWN', 'INST') NOT NULL,
    form_of_payment ENUM('CASH', 'INST') NOT NULL,
    PRIMARY KEY (student_no, academic_year, trimester, or_no)
);

I inserted some values:

INSERT INTO official_receipt(student_no , academic_year, trimester, or_no, issue_date, received_from, amount_of, issued_by, doc_type, form_of_payment)
VALUES
    (201201121, 'AY201314', '1', 029940, 2013-05-21, 'NAME', 20000.00, NULL, 'DOWN', 'INST'),
    (201201121, 'AY201314', '1', 029944, 2013-07-23, 'NAME', 8000.00, NULL, 'INST', 'INST'),
    (201201101, 'AY201314', '1', 029941, 2013-05-21, 'NAME', 56650.00, NULL, 'FULL', 'CASH'),
    (201201037, 'AY201314', '1', 029942, 2013-05-21, 'NAME', 56650.00, NULL, 'FULL', 'CASH'),
    (201201142, 'AY201314', '1', 029943, 2013-05-21, 'NAME', 63800.00, NULL, 'FULL', 'CASH');

I am getting this error:

Error Code: 1292. Incorrect date value: '1987' for column 'issue_date' at row 1

I am quite stumped because I already followed the YYYY-MM-DD format. Any help?

Gautier

Answer 1

As documented under Date and Time Literals:

MySQL recognizes DATE values in these formats:

• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the expression 2013-05-21 is not a valid MySQL date literal (it is in fact an arithmetic expression, consisting of two subtractions: it results in the integer 1987). In order to comply with one of the literal formats detailed above, you must either quote your date literal as a string and/or remove the delimiters.

Pneumoconiosis

It is barely apparent you're talking about missing quotes here. – Slowwitted

@ebyrob: That's because I'm not. There's a perfectly valid literal format for dates which does not require quotes: in this case one merely need remove the delimiters. – Pneumoconiosis

Answer 2

You are missing with ' single quotes around the issue_date values for my test it inserts the records successfully

Try this

INSERT INTO official_receipt(student_no , academic_year, trimester, or_no, issue_date, received_from, amount_of, issued_by, doc_type, form_of_payment)
VALUES
    (201201121, 'AY201314', '1', 029940, '2013-05-21', 'NAME', 20000.00, NULL, 'DOWN', 'INST'),
    (201201121, 'AY201314', '1', 029944, '2013-07-23', 'NAME', 8000.00, NULL, 'INST', 'INST'),
    (201201101, 'AY201314', '1', 029941, '2013-05-21', 'NAME', 56650.00, NULL, 'FULL', 'CASH'),
    (201201037, 'AY201314', '1', 029942, '2013-05-21', 'NAME', 56650.00, NULL, 'FULL', 'CASH'),
    (201201142, 'AY201314', '1', 029943, '2013-05-21', 'NAME', 63800.00, NULL, 'FULL', 'CASH');

Here is your fiddle

Mcadams

Thank you very much. That web app is very useful too. – Gautier

Answer 3

You need to put the date literal in quotes. The error message says 1987 because the unquoted date is being read as the expression 2013 minus 5 minus 21, which is 1987.

Your dates can be like this: '2013-05-21' or '20130521' or a couple other formats covered in the documentation.

Romansh