[sql server] 从字符串向 datetime 转换时失败
dbo_a 表
id int identity(1,1),
m_name varchar(20),
m_start datetime,
m_expire datetime
dbo_b
m_name varchar(20),
m_start datetime,
m_expire datetime
现在,要把dbo_a表中 ,过过期 m_expire>=getdate() 的记录移至dbo_b
使用游标:
declare @m_id int ,@m_name varchar(20), @m_start datetime ,@m_expire datetime
declare mycursor cursor scroll for
select * from dbo_a where m_expire>=getdate()
declare @i int
open mycursor
set @i= @@cursor_rows
while @i>0
begin
fetch next from mycursor into @m_id,@m_name,@_start,@m_expire --****提示 从字符串向 datetime 转换时失败
set @i=@i-1
insert into mail_history values (m_name,@_start,@m_expire)
if( @@fetch_status!=0)
begin
delete from mail_queue where M_id=@m_id
set @@fetch_status=0
end
end
close mycursor
deallocate mycursor
我想可能是,从游标行中读取的都是字符类型的
所以我做了下面的更改
////////////////
declare @m_id int ,@m_name varchar(20), @m_start varchar(50) ,@m_expire varchar(50) --这里更改成varchar类型
declare mycursor cursor scroll for
select * from dbo_a where m_expire>=getdate()
declare @i int
open mycursor
set @i= @@cursor_rows
while @i>0
begin
fetch next from mycursor into @m_id,@m_name,@_start,@m_expire
set @i=@i-1
insert into mail_history values (m_name,convert(datetime,@_start),convert(datetime,@m_expire)) ---*****这里提示从字符串向 datetime 转换时失败
if( @@fetch_status!=0)
begin
delete from mail_queue where M_id=@m_id
set @@fetch_status=0
end
end
close mycursor
deallocate mycursor
这是为什么啊??