SQL DML：不正确的日期值 (MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17793936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DML: Incorrect date value (MySQL)
提问by eggyal
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?
我很困惑，因为我已经遵循了 YYYY-MM-DD 格式。有什么帮助吗？
回答by eggyal
As documented under Date and Time Literals:
MySQL recognizes
DATEvalues 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
YYYYMMDDorYYMMDDformat, provided that the number makes sense as a date. For example,19830905and830905are interpreted as'1983-09-05'.
MySQL 识别
DATE以下格式的值：
作为
'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。允许使用“宽松”语法：任何标点字符都可以用作日期部分之间的分隔符。例如，'2012-12-31'，'2012/12/31'，'2012^12^31'，和'2012@12@31'是相等的。作为没有分隔符
'YYYYMMDD'或'YYMMDD'格式的字符串，前提是该字符串作为日期有意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但是'071332'是非法的（它有无意义的月份和日期部分）并变为'0000-00-00'。作为
YYYYMMDD或YYMMDD格式的数字，前提是该数字作为日期有意义。例如，19830905和830905被解释为'1983-09-05'。
Therefore, the expression 2013-05-21is 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.
因此，该表达式2013-05-21不是有效的 MySQL 日期文字（它实际上是一个算术表达式，由两个减法组成：结果为 integer 1987）。为了符合上面详述的文字格式之一，您必须将日期文字引用为字符串和/或删除分隔符。
回答by M Khalid Junaid
You are missing with 'single quotes around the issue_datevalues for my test it inserts the records successfully
你在我的测试值'周围缺少单引号issue_date它成功插入记录
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');
回答by Ed Gibbs
You need to put the date literal in quotes. The error message says 1987because the unquoted date is being read as the expression 2013 minus 5 minus 21, which is 1987.
您需要将日期文字放在引号中。错误消息说，1987因为未引用的日期被读取为表达式2013 minus 5 minus 21，即1987.
Your dates can be like this: '2013-05-21'or '20130521'or a couple other formats covered in the documentation.
您的日期可以是这样的：'2013-05-21'或者文档中'20130521'涵盖的其他几种格式。
