从 SQL SERVER 2008 中的字符串转换日期和/或时间时转换失败

2022-01-16 00:00:00 sql-server sql-server-2008-r2 date-conversion

我有下面的 SQL.

 UPDATE  student_queues
 SET  Deleted=0,  
      last_accessed_by='raja', 
      last_accessed_on=CONVERT(VARCHAR(24),'23-07-2014 09:37:00',113)
 WHERE std_id IN ('2144-384-11564') 
   AND reject_details='REJECT'

当我运行上述 SQL 时，抛出了以下异常.

when I ran the above SQL the below exception has been throwed.

从字符串转换日期和/或时间时转换失败.

推荐答案

如果您尝试插入 last_accessed_on，这是一个 DateTime2，那么您的问题是因为您将其转换为 SQL 无法理解的格式的 varchar.

If you're trying to insert in to last_accessed_on, which is a DateTime2, then your issue is with the fact that you are converting it to a varchar in a format that SQL doesn't understand.

如果您将代码修改为此，它应该可以工作，请注意您的日期格式已更改为:YYYY-MM-DD hh:mm:ss:

If you modify your code to this, it should work, note the format of your date has been changed to: YYYY-MM-DD hh:mm:ss:

UPDATE student_queues SET Deleted=0, last_accessed_by='raja', last_accessed_on=CONVERT(datetime2,'2014-07-23 09:37:00') WHERE std_id IN ('2144-384-11564') AND reject_details='REJECT'

或者如果你想使用CAST，替换为:

Or if you want to use CAST, replace with:

CAST('2014-07-23 09:37:00.000' AS datetime2)

这是使用 SQL ISO 日期格式.

相关文章