SQL DML: Incorrect date value (MySQL)
date-format, dml, insert-into, mysql
Solution
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.
Problem
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?