Not sure what you're trying to accomplish, But this works for me:

DECLARE @CharNum VARCHAR(20)
DECLARE @DeciNum DECIMAL(9,4)
SELECT @CharNum = '-12345.6789'
SELECT @DeciNum = CONVERT(DECIMAL(9,4), @CharNum)
SELECT @CharNum, @DeciNum

In fact there is _implicit_ conversion between VarChar and Decimal, so this works also (as long as @CharNum is actually numeric):

DECLARE @CharNum VARCHAR(20)
DECLARE @DeciNum DECIMAL(9,4)
SELECT @CharNum = '-12345.6789'
SELECT @DeciNum = @CharNum
SELECT @CharNum, @DeciNum


ALSO:

This will error:
DECLARE @Val VARCHAR(1) /* or anything other than INT */
SELECT @Val = 3 /* implicit conversion from INT to VarChar, BTW */
SELECT SUBSTRING(@CharNum,@Val,1)

but this won't:
DECLARE @Val INT
SELECT @Val = 3
SELECT SUBSTRING(@CharNum,@Val,1)

<Quoth the BOL>
SUBSTRING(expression, start, length)
Arguments
expression
Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start
Is an INTEGER that specifies where the substring begins.

length
Is an INTEGER that specifies the length of the substring (the number of characters or bytes to return).
<END QUOTE>


ALSO ALSO: It's already been said, but if you're simply trying to move decimals, just multiply or divide
-Greg


-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Thursday, October 30, 2003 10:34 AM
To: LazyDBA.com Discussion
Subject: How to convert a varchar to a number


Declare @time decimal
DECLARE @TMP_TIME VARCHAR(9)
DECLARE @TMP_TIME2 VARCHAR(9)
DECLARE @BIT bit

Select @bit = (SELECT (1- ABS( SIGN( ISNULL( 100 - @TIME, 1)))))
if @bit = 1 -- needed to find out whether I was dealing with a negative value, 1st.

SELECT @TMP_CNT = 0
SELECT @TMP_TIME = CONVERT(VARCHAR(9), @TIME)
SELECT @TMP_CNT = LEN(@TIME)
SELECT @TMP_CNT = @TMP_CNT - 2
SELECT @TMP_TIME2 = SUBSTRING(@TMP_TIME, @TMP_CNT, 2)
SELECT @TMP_TIME = SUBSTRING(@TMP_TIME, 1, @TMP_CNT )
SELECT @TMP_CNT = LEN(@TMP_TIME)
if @tmp_cnt > 1
BEGIN
IF @TMP_TIME2 = '15' SELECT @TMP_TIME2 = '25'
IF @TMP_TIME2 = '30' SELECT @TMP_TIME2 = '50'
IF @TMP_TIME2 = '45' SELECT @TMP_TIME2 = '75'
END
SELECT @TMP_TIME = ISNULL((@TMP_TIME + '.' + @TMP_TIME2), 1)
SELECT @TIME = CONVERT(NUMERIC, @TIME)
SELECT @TIME = CONVERT(@TIME AS INT)


I have found an alternative to this, but I am still curious, as to why you can't change a varchar to a negative number,
without erring???? And also if you can specify a location using a variable, in a substring command, it didn't seem to like
that either.

Thanks,

Susan



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page