usedate格式20091110102211,转换为datetime 2009-11-10 10:22:11
语句
cast((left(usedate,4) +'-'+ substring(usedate,5,2) +'-'+ substring(usedate,7,2)+' '+substring(usedate,9,2)+':'+substring(usedate,11,2)+':'+substring(usedate,13,2)) as datetime)
SQL2000查询分析器中运行正常,但在存储过程中报错:'-'附近有语法错误
请高手提点,谢谢!
语句
cast((left(usedate,4) +'-'+ substring(usedate,5,2) +'-'+ substring(usedate,7,2)+' '+substring(usedate,9,2)+':'+substring(usedate,11,2)+':'+substring(usedate,13,2)) as datetime)
SQL2000查询分析器中运行正常,但在存储过程中报错:'-'附近有语法错误
请高手提点,谢谢!
AS
exec('
INSERT INTO datain (usedate,customername,machineid,oc,serialnumber,state,ticket,infoback)
SELECT cast((left(usedate,4) +'-'+ substring(usedate,5,2) +'-'+ substring(usedate,7,2)+' '+substring(usedate,9,2)+':'+substring(usedate,11,2)+':'+substring(usedate,13,2)) as datetime),customername,machineid,convert(int,oc),serialnumber,case state when ''true'' then 1 else 0 end as state,ticket,infoback
FROM txtdata
')
GO
存储过程的语句
AS
exec('
INSERT INTO datain (usedate,customername,machineid,oc,serialnumber,state,ticket,infoback)
SELECT cast((left(usedate,4) +''-''+ substring(usedate,5,2) +''-''+ substring(usedate,7,2)+'' ''
+ substring(usedate,9,2)+'':''+substring(usedate,11,2)+'':''+substring(usedate,13,2)) as datetime),customername,machineid,convert(int,oc),serialnumber,case state when ''true'' then 1 else 0 end as state,ticket,infoback
FROM txtdata
')
GO--动态SQL中,字符串常量要用两个单引号括起来(''-'')
--在动态语句中拼接时,sql两点表一点,