Conversion failed - SQL 2005 - 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)

Conversion failed - SQL 2005

Author

Topic

noamway
Starting Member

7 Posts

Posted - 2006-09-16 : 14:00:13

I'm using MS-SQL 2005
And my table look like this:


messID    txt_root   		num_parent
111686    1            		0
111687    2             	0
111688    3                    	0
111689    001                  	3
111690    001.001              	3
111691    001.001.001          	3

And I'm running this function:


SELECT TOP 1 txt_root FROM support_DB
WHERE num_parent=0
ORDER BY CONVERT(int,txt_root) desc

And I'm getting this error:


Conversion failed when converting the varchar value '001.001' to data type int.

Thanks for your help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-16 : 14:17:57

you can only have one . in the varchar you want to convert to int.
try ORDER BY CONVERT(int, REPLACE(txt_root, '.', ''))

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 05:36:22

or you can rightpad the txt_root column values.

SELECT TOP 1 txt_root FROM support_DB
WHERE num_parent=0
ORDER BY RIGHT(REPLICATE('0', 200) + txt_root, 200) desc

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources