SQL DML:不正确的日期值(MySQL) [英] SQL DML: Incorrect date value (MySQL)

问题描述

我在数据库中创建了一个表:

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)
);

我插入了一些值:

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');

我收到此错误:

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

我很沮丧，因为我已经遵循YYYY-MM-DD格式.有帮助吗?

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

推荐答案

如日期和时间文字:

MySQL可以识别以下格式的 DATE 值:

• 作为'YYYY-MM-DD'或'YY-MM-DD'格式的字符串.允许使用宽松"语法:任何标点符号都可以用作日期部分之间的分隔符.例如，'2012-12-31'，'2012/12/31'，'2012^12^31'和'2012@12@31'是等效的.

• 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.

作为不带分隔符的字符串，其格式为'YYYYMMDD'或'YYMMDD'，但前提是该字符串作为日期有意义.例如，'20070523'和'070523'被解释为'2007-05-23'，但是'071332'是非法的(具有无意义的月份和日期部分)，并变为'0000-00-00'.

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'.

以YYYYMMDD或YYMMDD格式的数字表示，只要该数字作为日期有意义.例如，19830905和830905被解释为'1983-09-05'.

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'.

因此，表达式2013-05-21不是有效的MySQL日期文字(实际上是算术表达式，由两个减法组成:其结果为整数1987).为了符合上面详述的一种文字格式，您必须以字符串形式引用日期文字和/或删除定界符.

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.

这篇关于SQL DML:不正确的日期值(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！