从字符串转换为 datetime 时发生语法错误??!! 你在set @strDaySQL 时候的语句有问题。大致看了一下,我觉得你最好把所有的变量的一些逻辑关系都事先处理好,然后在转换成字符类型,拼接成最后的sql语句。你另外可以在执行该sql语句的前面,加上print @strDaySQL看看你最终的sql语句是什么 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 跟 踪下@strDaySQL的值就行了。 alter proc weekly3( @startdate datetime, -- 查询开始时间 @enddate datetime -- 查询结束时间)as declare @iDayNum int -- 查询的天数 declare @itempNum int -- 临时变量天数 declare @strDaySQL varchar(8000) -- 查询每天拨打量语句串 Set @strDaySQL='' Set @iDayNum=convert(int,@enddate-@startdate) Set @itempNum = 0-- set @strDaySQL =@strDaySQL + 'select convert(char(10),'+ @startdate + @itempNum +',120) as Date,-- datename(dw,' + @startdate + @itempNum + ') as WeekDay,-- count(*) as CallerNum,-- count(distinct caller) as CallerNum-- from fc_cdr where starttime >= ' + @startdate + @itempNum + '-- and starttime<' + @startdate + @itempNum + '+1'/*上面一段代码改成下面的*/ set @strDaySQL =@strDaySQL + 'select '+ convert(char(10),@startdate + @itempNum ,120)+' as Date,' +datename(dw, @startdate + @itempNum ) +' as WeekDay, count(*) as CallerNum, count(distinct caller) as CallerNum from fc_cdr where starttime >= ' +convert(char(10),@startdate + @itempNum ,120) +' and starttime< ' +convert(char(10),@startdate + @itempNum+1 ,120) set @itempNum=@itempNum+1 while @itempNum<=@iDayNum begin -- set @strDaySQL =@strDaySQL + 'union select convert(char(10),'+ @startdate +'+'+ @itempNum +',120) as Date,-- datename(dw,' + @startdate +'+' + @itempNum + ') as WeekDay,-- count(*) as CallerNum,-- count(distinct caller) as CallerNum-- from fc_cdr where starttime >= ' + @startdate +'+' + @itempNum + '-- and starttime<' + @startdate +'+' + @itempNum + '+1'/*上面一段改成下面的*/ set @strDaySQL =@strDaySQL + 'union select '+ convert(char(10),@startdate + @itempNum ,120)+' as Date,' +datename(dw, @startdate + @itempNum ) + 'as WeekDay, count(*) as CallerNum, count(distinct caller) as CallerNum from fc_cdr where starttime >= ' +convert(char(10),@startdate + @itempNum ,120)+' and starttime<' +convert(char(10),@startdate + @itempNum ,120) Set @itempNum = @itempNum+1 end set @strDaySQL = stuff(@strDaySQL,1,6,'') exec(@strDaySQL) 用了一下午,解决了~~`串字符串真麻烦啊!!! while @itempNum<=@iDayNum begin set @strDaySQL =@strDaySQL + ' union select '''+ convert(varchar(10),(@startdate + @itempNum),120) +''' as Date, datename(dw,''' + convert(varchar(10),(@startdate + @itempNum),120) + ''') as WeekDay, count(*) as CallNum, count(distinct caller) as CallerNum from fc_cdr where starttime >= ''' + convert(varchar(10),(@startdate + @itempNum),120) + ''' and starttime< ''' + convert(varchar(10),(@startdate + @itempNum + 1),120) +'''' Set @itempNum = @itempNum+1 end set @strDaySQL = stuff(@strDaySQL,1,6,'') EXEC(@strDaySQL) 关于DEFAULT的问题 怎么理解数据库中元组关系语言的用存在量词的检索 sql2008无日志文件附加数据库失败,无法打开数据库 'xxx' 版本 611。请将该数据库升级为最新 谁来拯救我?如何按照某列的某值来统计数据? SQL要怎么改呀???? 如此查询,一个过分的要求? 如何选择规定数量的记录 求救(向蚂蚁大力愉快的登山者,邹键等高手求救 急询,这样的触发器该怎么写??? sql2005删除不掉某些数据? 存储过程与用户问题 Sql时间差的问题!急,在线等
(
@startdate datetime, -- 查询开始时间
@enddate datetime -- 查询结束时间
)
as
declare @iDayNum int -- 查询的天数
declare @itempNum int -- 临时变量天数
declare @strDaySQL varchar(8000) -- 查询每天拨打量语句串
Set @strDaySQL=''
Set @iDayNum=convert(int,@enddate-@startdate)
Set @itempNum = 0
-- set @strDaySQL =@strDaySQL + 'select convert(char(10),'+ @startdate + @itempNum +',120) as Date,
-- datename(dw,' + @startdate + @itempNum + ') as WeekDay,
-- count(*) as CallerNum,
-- count(distinct caller) as CallerNum
-- from fc_cdr where starttime >= ' + @startdate + @itempNum + '
-- and starttime<' + @startdate + @itempNum + '+1'
/*上面一段代码改成下面的*/
set @strDaySQL =@strDaySQL + 'select '+ convert(char(10),@startdate + @itempNum ,120)+' as Date,'
+datename(dw, @startdate + @itempNum ) +' as WeekDay,
count(*) as CallerNum,
count(distinct caller) as CallerNum
from fc_cdr where starttime >= ' +convert(char(10),@startdate + @itempNum ,120) +'
and starttime< ' +convert(char(10),@startdate + @itempNum+1 ,120)
set @itempNum=@itempNum+1
while @itempNum<=@iDayNum
begin
-- set @strDaySQL =@strDaySQL + 'union select convert(char(10),'+ @startdate +'+'+ @itempNum +',120) as Date,
-- datename(dw,' + @startdate +'+' + @itempNum + ') as WeekDay,
-- count(*) as CallerNum,
-- count(distinct caller) as CallerNum
-- from fc_cdr where starttime >= ' + @startdate +'+' + @itempNum + '
-- and starttime<' + @startdate +'+' + @itempNum + '+1'
/*上面一段改成下面的*/ set @strDaySQL =@strDaySQL + 'union select '+ convert(char(10),@startdate + @itempNum ,120)+' as Date,'
+datename(dw, @startdate + @itempNum ) + 'as WeekDay,
count(*) as CallerNum,
count(distinct caller) as CallerNum
from fc_cdr where starttime >= ' +convert(char(10),@startdate + @itempNum ,120)+'
and starttime<' +convert(char(10),@startdate + @itempNum ,120) Set @itempNum = @itempNum+1
end set @strDaySQL = stuff(@strDaySQL,1,6,'') exec(@strDaySQL)
串字符串真麻烦啊!!!
while @itempNum<=@iDayNum
begin
set @strDaySQL =@strDaySQL + ' union select '''+ convert(varchar(10),(@startdate + @itempNum),120) +''' as Date,
datename(dw,''' + convert(varchar(10),(@startdate + @itempNum),120) + ''') as WeekDay,
count(*) as CallNum,
count(distinct caller) as CallerNum
from fc_cdr where starttime >= ''' + convert(varchar(10),(@startdate + @itempNum),120) + '''
and starttime< ''' + convert(varchar(10),(@startdate + @itempNum + 1),120) +'''' Set @itempNum = @itempNum+1
end set @strDaySQL = stuff(@strDaySQL,1,6,'')
EXEC(@strDaySQL)