从字符串转换datetime时转换失败。 [英] Conversion failed when converting datetime from character string.

问题描述

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时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！