TSQL Convert Problem

[MMund]

I need to populate a numeric field hours with data from a varchar string s. To do this, I use the following code, which is contained within an INSERT statement:

hours = convert(numeric(7,2),replicate('0',7 - len(ltrim(substring(s,14,7))))+ltrim(substring(s,14,7)))

This works UNLESS the number I read out of the string is negative, in which case I get an error "Error converting varchar to numeric".

Ideas, anyone?

TIA,

MMund

 

[bborissov]

And how hours can be negative?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MMund]

The data is coming from an old FoxPro 2.6 system, where the user can enter them that way.

 

[pjw001]

My guess is that you are giving convert a string of the form 0000-42.

You will need to take some action to deal with the -.

 

[RyanEK]

Can you provide an example of the string data?

 

[MMund]

0623AR VAC -7.500385 2008063020080706 A1 A

The negative amount is -7.50. The numbers just to the right are a different field. The field in question actually begins just after the "VAC".

 

[bborissov]

DECLARE @Test varchar(8000)
SET @Test = '0623AR    VAC  -7.500385 ...'
SELECT RIGHT('00000'+REPLACE(ltrim(substring(@Test,14,7)),'-',''),5)
SET @Test = '0623AR    VAC   7.500385 ...'
SELECT RIGHT('00000'+REPLACE(ltrim(substring(@Test,14,7)),'-',''),5)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.