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


MS Sql Server LazyDBA home page