请帮忙看一下有关日期类型的数据的转换问题

Question

declare @eid int   --用户ID
declare @bgndate smalldatetime --开始日期
declare @enddate smalldatetime --结束日期
declare @otherwhere nvarchar(200)
set @eid = 1
set @bgndate = convert(smalldatetime,'2005-12-1')
set @enddate = convert(smalldatetime,'2007-1-1')
set @otherwhere = N' status=-3 '
DECLARE @sql nvarchar(1000)
DECLARE @tmpError int
SET @tmpError = 0DECLARE @tmpsectchck int  
DECLARE @tmpprovinchck int  
DECLARE @tmpplaceincomp nvarchar(30)
DECLARE @tmpydate int  
DECLARE @tmpgrade int  DECLARE @ma_6_1 nvarchar(2000)
set @ma_6_1 =N''                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   SET @sql = 'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR SELECT grade,placeincomp,Year(bgndate) FROM problem_record WHERE recscid=31 AND receid'+'='+convert(varchar(20),@eid)+N' AND bgndate>=convert(smalldatetime,'+convert(nvarchar(20),@bgndate)+N') AND enddate<convert(smalldatetime'+convert(nvarchar(20),@enddate)+N')   '+N' AND  '+@otherwhere
EXEC(@sql)OPEN cursor_problemrecord
FETCH NEXT FROM cursor_problemrecord 
INTO @tmpgrade,@tmpplaceincomp,@tmpydate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ma_6_1 = @ma_6_1 +CONVERT(nvarchar(4),@tmpydate)+ N'#'+CONVERT(nvarchar(4),@tmpgrade)+ N'#'+@tmpplaceincomp+ N'@'
FETCH NEXT FROM cursor_problemrecord 
INTO @tmpgrade,@tmpplaceincomp,@tmpydate
END
CLOSE cursor_problemrecord
DEALLOCATE cursor_problemrecord print'@ma_6_1='+@ma_6_1
请帮忙看一下变量@sql中游标声明中的两个日期类型的变量bgndate和enddate的声明有什么错误，因为我把变量@sql的值改为如下：
SET @sql = 'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR SELECT grade,placeincomp,Year(bgndate) FROM problem_record WHERE recscid=31 AND receid='+convert(nvarchar(20),@eid)+N' AND  '+@otherwhere
时执行是正确的，但如国有日期设置加进去，语法是没有问题，但执行时返回：游标不存在的错误

Answer 1

CONVERT函数有问题，这样试试SET @sql = 'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR 
SELECT grade,placeincomp,Year(bgndate) FROM problem_record 
WHERE recscid=31 AND receid='+convert(varchar(20),@eid)+N' 
AND bgndate>='+convert(nvarchar(20),@bgndate,110)+N'
AND enddate<'+convert(nvarchar(20),@enddate,110)+N' 
AND  '+@otherwhere
exec(@sql)

Answer 2

ls说的对
convert用于时间转换的时候，写法是应该像这样convert(nvarchar(20),@bgndate,110)
只有里面的数字110表示转换后的时间表示格式“mm-dd-yy”建议lz在联机帮助查看一下convert的用法，里面有详细的表格：）

Answer 3

declare @eid int   --用户ID
declare @bgndate smalldatetime --开始日期
declare @enddate smalldatetime --结束日期
declare @otherwhere nvarchar(200)
set @eid = 1
set @bgndate = convert(smalldatetime,'2005-12-1')
set @enddate = convert(smalldatetime,'2007-1-1')
set @otherwhere = N' status=-3 '
DECLARE @sql nvarchar(8000)
DECLARE @tmpError int
SET @tmpError = 0DECLARE @tmpsectchck int  
DECLARE @tmpprovinchck int  
DECLARE @tmpplaceincomp nvarchar(30)
DECLARE @tmpydate int  
DECLARE @tmpgrade int  DECLARE @ma_6_1 nvarchar(2000)
set @ma_6_1 =N''                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   SET @sql = 'DECLARE cursor_problemrecord INSENSITIVE CURSOR FOR SELECT grade,placeincomp,Year(bgndate) FROM problem_record WHERE recscid=31 AND receid'+'='+convert(varchar(20),@eid)+N' AND bgndate>=convert(smalldatetime,'+convert(nvarchar(20),@bgndate)+N') AND enddate<convert(smalldatetime'+convert(nvarchar(20),@enddate)+N')   '+N' AND  '+@otherwhere
+'OPEN cursor_problemrecord
FETCH NEXT FROM cursor_problemrecord 
INTO @tmpgrade,@tmpplaceincomp,@tmpydate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ma_6_1 = @ma_6_1 +CONVERT(nvarchar(4),@tmpydate)+ N''#''+CONVERT(nvarchar(4),@tmpgrade)+ N''#''+@tmpplaceincomp+ N''@''
FETCH NEXT FROM cursor_problemrecord 
INTO @tmpgrade,@tmpplaceincomp,@tmpydate
END
CLOSE cursor_problemrecord
DEALLOCATE cursor_problemrecord'
EXEC(@sql)print'@ma_6_1='+@ma_6_1
-------------
这样试一下