现场给分，从字符串转换为 datetime 时发生语法错误。

[zzz1975]

insert into [WiseCRM_NBS]..Activity (
activityId,accountId,contactId,topic,activityTypeCode,startTime,endTime,allDayEvent,duration,alertTime,state,status,createdOn,createdBy,modifiedBy,owningUser,owningBusinessUnit,description,c__user21,c__user22,c__user23,c__user24,c__user25,c__dict21,c__dict22,c__dict23,c__dict24,c__dict25
)
select
dbo.makeId(8, activity_id),dbo.makeId(2, company_id),dbo.makeId(3, contact_id),isnull(regarding, '_'),
case activity_type
when 'N' then 1
when 'A' then 2
when 'P' then 3
when 'T' then 4
when 'M' then 5
else 1
end
,
convert(datetime, (convert(nvarchar, start_date, 112) + ' ' + start_time), 112)
,
convert(datetime,(convert(nvarchar, end_date, 112) + ' ' + end_time), 112)
,0,duration_hours,case when reminder_flag = 1 then reminder_date else null end,
case
when closed = 1 and held = 1 then 2
when closed = 1 and held = 0 then 1
else 0
end
,
case
when closed = 1 and held = 1 then 2000
when closed = 1 and held = 0 then 1000
else 0
end
,create_date,dbo.getUserIdByName(operator),dbo.getUserIdByName(last_operator),dbo.getUserIdByName(operator),dbo.makeZeroId(30),details,user21,user22,user23,user24,user25,dict21_id,dict22_id,dict23_id,dict24_id,dict25_id
from [wisecrm]..activitiesHistory
执行报错

服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。

希望指正，最好能测试，谢谢。自己找不出错误了

[百年树人]

最好提供表结构，测试数据，你的算法和你要的结果

[guguda2008]

convert(datetime, (convert(nvarchar, start_date, 112) + ' ' + start_time), 112)
,
convert(datetime,(convert(nvarchar, end_date, 112) + ' ' + end_time), 112)
肯定是这两句的问题，先别转换原样输出看看有什么问题

[--小F--]

start_date是不是能够转换成日期字段的??

[zzz1975]

start_date start_time都是字段

[zzz1975]

插入表字段 startTime,endTime 选自表为字符start_date， start_time 组成带时间日期对应startTime

[htl258_Tony]

convert(datetime, (convert(nvarchar, start_date, 112) + ' ' + start_time))
,
convert(datetime,(convert(nvarchar, end_date, 112) + ' ' + end_time))

[htl258_Tony]

 convert(datetime, (convert(nvarchar, start_date, 112) + ' ' + start_time))
  ,
  convert(datetime,(convert(nvarchar, end_date, 112) + ' ' + end_time))

改为这样。

[zzz1975]

1258 你写和我没有区别，我把你拷贝进去还是错误，呵呵

[永生天地]

insert into [WiseCRM_NBS]..Activity (
activityId,accountId,contactId,topic,activityTypeCode,startTime,endTime,allDayEvent,duration,alertTime,state,status,createdOn,createdBy,modifiedBy,owningUser,owningBusinessUnit,description,c__user21,c__user22,c__user23,c__user24,c__user25,c__dict21,c__dict22,c__dict23,c__dict24,c__dict25
)
问题好像在这里面还有datatime类型的字段。。。

[zzz1975]

还有意见吗？20分钟后结贴，希望参与指正

[htl258_Tony]

convert(datetime, (convert(nvarchar, start_date, 23) + ' ' + ltrim(start_time)))
  ,
  convert(datetime,(convert(nvarchar, end_date, 23) + ' ' + ltrim(end_time)))

try

[王向飞]

convert(datetime, (convert(nvarchar, start_date, 112) + ' ' + start_time), 112)
,
convert(datetime,(convert(nvarchar, end_date, 112) + ' ' + end_time), 112)
肯定就在这啊

[老黎]

把convert(nvarchar, start_date, 112) + ' ' + start_time
print出来看看是否符合时间格式

[lrjt1980]

????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

[tvinni]

convert(datetime, (convert(varchar(30), start_date, 112) + ' ' + start_time))
,
convert(datetime,(convert(varchar(30), end_date, 112) + ' ' + end_time))