|
我在我的数据库中创建了一个表：
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',PRIMARY KEY (student_no,academic_year,trimester,or_no)
);
我插入了一些值：
INSERT INTO official_receipt(student_no,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,'INST'),(201201121,029944,2013-07-23,8000.00,'INST',(201201101,029941,56650.00,'FULL','CASH'),(201201037,029942,(201201142,029943,63800.00,'CASH');
我收到此错误：
Error Code: 1292. Incorrect date value: '1987' for column 'issue_date' at row 1
我很难过,因为我已经遵循了YYYY-MM-DD格式.有帮助吗？
最佳答案
如Date and Time Literals所述：
MySQL recognizes 07001 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'.
因此,表达式2013-05-21不是有效的MySQL日期文字(它实际上是一个算术表达式,由两个减法组成：它产生整数1987).为了符合上面详述的文字格式之一,您必须将日期文字引用为字符串和/或删除分隔符.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
