Error converting data type varchar to bigint.

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)

Error converting data type varchar to bigint.

Author

Topic

jmodiba
Starting Member

7 Posts

Posted - 2008-11-25 : 06:58:19

hi this script is being on my production environment for a while since yesterday it bomb out with following error can anyone help please

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

this error is as result of this statement which is part of the whole statement
and
convert(bigint,ct.Reference6) = 1

SELECT
ct.ID,
convert(smalldatetime, substring(ct.Reference10,0,11), 120) as Reference10,
ltrim(rtrim(ct.Details)) as Details,
ct.Amount,
cb.AccountID
FROM
CashBookTransaction ct
inner join
CashBookBatch cb on ct.CashBookBatchID = cb.ID
inner join
Account a on cb.AccountID = a.ID
WHERE
a.DomainID = @CurrentDomainID
and
ct.CashbookTransactionTypeID = 1
and
convert(bigint,ct.Reference6) = 1
and
isnumeric(ct.reference6) = 1
and
Reference5 = '-1'
and
ct.ReconID is null
ORDER BY
2,3,4,5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 07:01:19

SELECT * FROM CashBookTransaction
WHERE ISNUMERIC(reference6) = 0

too see which varchar values cannot be translated into a numeric value, regardless of truncation or rounding.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-25 : 07:23:51

or

SELECT * FROM CashBookTransaction
WHERE reference6 like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources