本文介绍了从字符串转换datetime时转换失败。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER procedure [dbo].[sp_insertstaffattendence]( @staffid varchar(50), @name varchar(50), @date datetime, @morningstatus char, @eveningstatus char, @type varchar(20) ) as begin if(@type = 'insert') begin declare @subject as varchar(50), @acadamicyear as varchar(20), @stmt as varchar(max) select @subject=Subject from sc_staff_tbl where StaffId=@staffid set @acadamicyear = (select presentacadamicyear from sc_school_general) set @stmt= 'insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('+@staffid+','+@name+','+@date+','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')' select @stmt --exec ('insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('+@staffid+','+@name+','+@date+','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')') end else begin exec('update sc_staffattendence'+@acadamicyear+' set Morningstatus='+@morningstatus+',Eveningstatus='+@eveningstatus+' where StaffId='+@staffid+' and year(Date)=year('+@date+') and month(Date)=month('+@date+') and day(Date)=day('+@date+')') end end
推荐答案
乍一看, @date 似乎是空的。确保你在程序的某个地方定义了这个值。 At first glance, @date seems to be empty. Make sure you are defining this value somewhere in the procedure.
谢谢你我解决了。 thank you i solved. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go alter procedure [dbo].[sp_insertstaffattendence]( @staffid varchar(50), @name varchar(50), @date datetime, @morningstatus char, @eveningstatus char, @type varchar(20) ) as begin if(@type = 'insert') begin declare @subject as varchar(50), @acadamicyear as varchar(20), @stmt as varchar(max) set @subject='demo' set @acadamicyear = '2012_2013' set @stmt= 'insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('''+@staffid+''','''+@name+''','''+convert(varchar(50),@date)+''','''+@morningstatus+''','''+@eveningstatus+''','''+@subject+''',''Y'')' select @stmt --exec(@stmt) --exec ('insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('''+@staffid+''','''+@name+''','''+convert(varchar(20),@date)+''','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')') end else begin exec('update sc_staffattendence'+@acadamicyear+' set Morningstatus='+@morningstatus+',Eveningstatus='+@eveningstatus+' where StaffId='+@staffid+' and year(Date)=year('+@date+') and month(Date)=month('+@date+') and day(Date)=day('+@date+')') end end
'''+ convert(VARCHAR(20),GETDATE()(或)你要转换的内容,101)+ ''' 这用于将datetime转换为varchar '''+convert(VARCHAR(20),GETDATE()(or)what you want convert ,101)+''' This is used to convert datetime to varchar
更多推荐
从字符串转换datetime时转换失败。
发布评论