SQL插入datetime数据报错：varchar转datetime出现超出范围值

这个问题很常见，我来帮你分析解决：

首先，你执行的插入语句是：

INSERT INTO Tasks(TaskCreateDate, TaskDescription, TaskFinnishDate, TaskArchiveDate, GroupID, EmployeeID) 
VALUES 
('20/01/2019', 'abc', '21/01/2019', '21/01/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='1'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
('21/02/2019', 'abc', '22/02/2019', '22/02/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='2'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
('22/03/2019', 'abc', '23/03/2019', '23/03/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='3'), (SELECT EmployeeID from Employees WHERE EmployeeID='1'));

触发的错误提示是：

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

错误原因 #

这个报错的核心是日期格式和SQL Server的默认解析规则不匹配。比如你的数据库默认使用美式日期格式（MM/DD/YYYY），那20/01/2019会被尝试解析为“第20个月，1日”——显然月份不可能超过12，所以直接触发了范围错误。

解决方案 #

给你三个靠谱的解决思路，按需选择：

• 方案1：使用ISO标准日期格式（推荐）
  YYYY-MM-DD是SQL Server全局通用的日期格式，不管服务器的区域设置如何，都能被正确解析。修改后的SQL如下：

  INSERT INTO Tasks(TaskCreateDate, TaskDescription, TaskFinnishDate, TaskArchiveDate, GroupID, EmployeeID) 
  VALUES 
  ('2019-01-20', 'abc', '2019-01-21', '2019-01-21', (SELECT GroupID from TypeOfGroup WHERE GroupID='1'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  ('2019-02-21', 'abc', '2019-02-22', '2019-02-22', (SELECT GroupID from TypeOfGroup WHERE GroupID='2'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  ('2019-03-22', 'abc', '2019-03-23', '2019-03-23', (SELECT GroupID from TypeOfGroup WHERE GroupID='3'), (SELECT EmployeeID from Employees WHERE EmployeeID='1'));
  

• 方案2：用CONVERT函数指定格式解析
  如果必须保留DD/MM/YYYY的格式，可以用CONVERT函数明确告诉SQL Server要按日/月/年的规则解析，格式代码用103（对应英式/欧式日期）：

  INSERT INTO Tasks(TaskCreateDate, TaskDescription, TaskFinnishDate, TaskArchiveDate, GroupID, EmployeeID) 
  VALUES 
  (CONVERT(DATETIME, '20/01/2019', 103), 'abc', CONVERT(DATETIME, '21/01/2019', 103), CONVERT(DATETIME, '21/01/2019', 103), (SELECT GroupID from TypeOfGroup WHERE GroupID='1'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  (CONVERT(DATETIME, '21/02/2019', 103), 'abc', CONVERT(DATETIME, '22/02/2019', 103), CONVERT(DATETIME, '22/02/2019', 103), (SELECT GroupID from TypeOfGroup WHERE GroupID='2'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  (CONVERT(DATETIME, '22/03/2019', 103), 'abc', CONVERT(DATETIME, '23/03/2019', 103), CONVERT(DATETIME, '23/03/2019', 103), (SELECT GroupID from TypeOfGroup WHERE GroupID='3'), (SELECT EmployeeID from Employees WHERE EmployeeID='1'));
  

• 方案3：临时修改会话的日期格式
  可以在执行插入语句前，先设置当前会话的日期解析规则为DMY，这样SQL Server就会按日/月/年的顺序处理字符串：

  SET DATEFORMAT DMY;
  INSERT INTO Tasks(TaskCreateDate, TaskDescription, TaskFinnishDate, TaskArchiveDate, GroupID, EmployeeID) 
  VALUES 
  ('20/01/2019', 'abc', '21/01/2019', '21/01/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='1'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  ('21/02/2019', 'abc', '22/02/2019', '22/02/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='2'), (SELECT EmployeeID from Employees WHERE EmployeeID='1')), 
  ('22/03/2019', 'abc', '23/03/2019', '23/03/2019', (SELECT GroupID from TypeOfGroup WHERE GroupID='3'), (SELECT EmployeeID from Employees WHERE EmployeeID='1'));
  

  注意：这个设置只对当前数据库连接会话有效，不会影响其他用户或连接的设置。

内容的提问来源于stack exchange，提问作者user9729956