data conversion error - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

data conversion error

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-27 : 08:22:40

Can anyone please help on this one. When I try to convert data type varchar to bigint I receive the below error.

Here's the T-SQL:
SELECT sum(revturn) as 'ttm'
from #res2
Where TheDate = @NextQDate
SELECT cast ('ttm' as bigint)

Here's what I get in Query Analyzer:
Server: Msg 8114, Level 16, State 5, Procedure rev, Line 82
Error converting data type varchar to bigint.

Any help would be appreciated,
Cheers.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-09-27 : 08:41:46

Your second query is trying to cast the literal string 'ttm' as bigint. Try this:

SELECT sum(cast(revturn as bigint)) as 'ttm' 
from #res2
Where TheDate = @NextQDate

Mark

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-27 : 09:06:48

That worked great! So the same thing for the next line, i'm trying to divide the 'ttm'into a new variable and I am getting the same message. So I have to do the conversion on @Shares:

select ('ttm' / @Shares) as 'per'

I still get :
Error converting data type varchar to bigint.
but I declared @Shares as BIGINT in my procedure so It would seem i'm using like data. What gives?

Thanks again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 09:22:04

Try this

Select ttm/(cast(@Shares as bigint)) as 'per' from yourTable

Madhivanan

Failing to plan is Planning to fail

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-27 : 09:34:22

I had to use '' around ttm.

Select 'ttm'/(cast(@Shares as bigint)) as 'per' from #res2

and i'm still getting:
Server: Msg 8114, Level 16, State 5, Procedure rev, Line 83
Error converting data type varchar to bigint.

Any ideas?
Cheers....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 09:38:40

What do you mean by 'ttm'?

Madhivanan

Failing to plan is Planning to fail

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-27 : 09:41:36

I have named the computation as 'ttm' so I can reference it though asp.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 09:45:15

I think you need this

SELECT sum(cast(revturn as bigint))/cast(@Shares as bigint) as 'per'
from #res2
Where TheDate = @NextQDate

Madhivanan

Failing to plan is Planning to fail

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-09-27 : 09:48:46

Thanks a bunch. It worked great...

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources