从 SQL SERVER 2008 中的字符串转换日期和/或时间时转换失败 [英] Conversion failed when converting date and/or time from character string in SQL SERVER 2008

问题描述

我有下面的 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 日期格式.

这篇关于从 SQL SERVER 2008 中的字符串转换日期和/或时间时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！