由于类型转换失败、密钥冲突等原因，无法运行 SQL 查询

Question

尝试在MS Access中运行此SQL查询时出现错误：

INSERT INTO Learner (learnerPersonIDPKFK, registrationDate)
VALUES (1, 21/09/2015);
INSERT INTO Learner (learnerPersonIDPKFK, registrationDate, recommendedByLearnerPersonIDFK)
VALUES (2, 05/03/2016, 1);

这是错误：

在此处输入图像描述

Learner表如下所示：

CREATE TABLE Learner
(
      learnerPersonIDPKFK INT NOT NULL PRIMARY KEY,
      registrationDate DATETIME,
      recommendedByLearnerPersonIDFK INT NOT NULL,
      CONSTRAINT fk_recommendedByLearnerPersonIDFK
          FOREIGN KEY(recommendedByLearnerPersonIDFK)
          REFERENCES Learner (learnerPersonIDPKFK)
);

Answer 1

尝试将日期括在单引号中：

INSERT INTO Learner (learnerPersonIDPKFK, registrationDate)
    VALUES (1, '21/09/2015');
INSERT INTO Learner (learnerPersonIDPKFK, registrationDate, recommendedByLearnerPersonIDFK)
    VALUES (2, '05/03/2016', 1);

根据日期时间格式的本地化设置，您可能需要使用 MM/DD/YYYY 格式。请记住，你传递一个 varchar 并让 SQL 尝试"猜测"如何隐式转换它：

INSERT INTO Learner (learnerPersonIDPKFK, registrationDate)
    VALUES (1, '09/21/2015');
INSERT INTO Learner (learnerPersonIDPKFK, registrationDate, recommendedByLearnerPersonIDFK)
    VALUES (2, '03/05/2016', 1);

使用一个简单的测试器来验证SQL是否可以"猜测"你的日期格式：

SELECT CAST('09/21/2015' AS DATETIME)