Question

sql 2008 插入日期时间 数据库报错：从字符串转换日期和/或时间时，转换失败。

insert into [ORIENT_TRADE].[dbo].[TRD_MEMBER_MESSAGE](
[MessageSubject]
,[MessageContent]
,[MessageObjectType]
,[MessageObjectId]
,[ReceiverMember]
,[ReceiverContactPerson]
,[SenderMember]
,[SenderContactPerson]
,[MessageIp]
,[MessageOrigin]
,[CreateDateTime]
,[ReadFlag]
,[ReplyFlag]
,[ReplyDateTime]
,[ReplyMessagePKId]
,[SpamFlag]
,[ReceiverDeleteFlag]
,[SenderDeleteFlag]
,[MessageType]) values ('mS','Mc',1,NULL,1000210,870000147,1000204,870000137,'10.0.0.1','CN',cast('2011-08-26 14:26:12.000' As datetime),0,0,'NULL',66,0,0,0,0)
,[CreateDateTime]为datetime类型

Answer 1

不用转换类型 直接用字符串 系统会默认转换的

insert into [ORIENT_TRADE].[dbo].[TRD_MEMBER_MESSAGE](
[MessageSubject]
,[MessageContent]
,[MessageObjectType]
,[MessageObjectId]
,[ReceiverMember]
,[ReceiverContactPerson]
,[SenderMember]
,[SenderContactPerson]
,[MessageIp]
,[MessageOrigin]
,[CreateDateTime]
,[ReadFlag]
,[ReplyFlag]
,[ReplyDateTime]
,[ReplyMessagePKId]
,[SpamFlag]
,[ReceiverDeleteFlag]
,[SenderDeleteFlag]
,[MessageType]) values ('mS','Mc',1,NULL,1000210,870000147,1000204,870000137,'10.0.0.1','CN','2011-08-26 14:26:12.000' ,0,0,'NULL',66,0,0,0,0)

追问

没有用的。。
还是一样报错。。
里面一条数据为 2011-05-20 00:00:00.000

追答

不可能的 你把报错截图上传 我看看

Answer 2

cast('2011-08-26 14:26:12.000' As datetime),
换成这个
to_date('2011-08-26 14:26','yyyy-mm-dd hh24:mm')