Error converting data type varchar to numeric.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error converting data type varchar to numeric.

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-12-10 : 13:02:51

I keep getting this error message when I am running this query:
Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.

The error message points to this line:

select count(distinct appid) participants,

Here is the query below:

Use OSPR_WorkArea;

Declare @BeginDte Smalldatetime; --<<<< Enter the beginning time frame
Set @BeginDte = '07/01/2011'; --<<<< Enter date in this format: '04/01/2007'
Declare @EndDte Smalldatetime; --<<<< Enter the ending time frame
Set @EndDte = '06/30/2012'; --<<<< Enter date in this format: '04/30/2007'
drop table #temp
select left(@BeginDte,11) 'Begin Date',left(@EndDte,11) 'End Date'

--get all participants
select appid
into #temp
from DOLETA.dbo.wp_9002_20114 wp
where 1=1
and rgn = '01'
and Black = '1'
and Gender = '1'
and convert(smalldatetime,
convert(varchar(10),programparticipationdate, 112))
<= @EndDte
and (convert(smalldatetime,
convert(varchar(10),programexitdate, 112))
>= @BeginDte or programexitdate is null)
--get number of participants
select count(distinct appid) participants,
s2p.srvs2partx,
plx.partxPlaced,
ref.ref2svcs
from #temp tmp
,
(
--get number of services
select count(*) srvs2partx
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
where 1=1
and svp.programid = 3
) s2p
,
(
--get number of placements
select count(*) partxPlaced
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
and servicecode between '750' and '882'
and programid = 3
) Plx
,
(
select count(*) ref2svcs
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
and servicecode in ('210','211')
and programid = 3
) Ref
group by
s2p.srvs2partx,plx.partxPlaced,ref.ref2svcs

--drop table #temp

Roger DeFour

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 13:33:08

Won't this be enough

Use OSPR_WorkArea 

Declare @BeginDte Smalldatetime; --<<<< Enter the beginning time frame 
Set @BeginDte = '07/01/2011'; --<<<< Enter date in this format: '04/01/2007' 
Declare @EndDte Smalldatetime; --<<<< Enter the ending time frame 
Set @EndDte = '06/30/2012'; --<<<< Enter date in this format: '04/30/2007' 

 
Select appid,
Count(distinct wp.appid) participants,
Count(svp.*) srvs2partx ,
SUM(Case When servicecode between '750' and '882' then 1 Else 0 End) partxPlaced,
SUM(Case When servicecode in ('210','211') then 1 Else 0 End) ref2svcs,
from DOLETA.dbo.wp_9002_20114 wp
inner join efmextract.dbo.serviceplan svp on wp.appid = svp.programappid and svp.programid = 3
and rgn = '01'
and Black = '1'
and Gender = '1'
and programparticipationdate <= @EndDte 
and (programexitdate >= @BeginDte or programexitdate is null))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources