Sql Server Bug or Feature? Decimal Numbers Conversion

Converting Varchar to Decimal Error in SQL

You need to convert that before the division, or use 100.0. It's trying to convert to INTEGER because 100 is an integer.

select '16.50' / 100.0
select try_convert(decimal(4,2),'16.50') / 100

If you know all of your varchar values can be implictly converted to decimal, then simply:

update bplateinfo2018
  Set [New O Swing Percentage] = [Original O Swing Percentage] / 100.0

Otherwise, you will need TRY_CONVERT or something similar.

update bplateinfo2018
  Set [New O Swing Percentage] = [Original O Swing Percentage] / 100.0
  where try_convert(decimal(16,4),[Original O Swing Percentage]) is not null

Decimal conversion scale errors

The answer is in MSDN.

decimal[ (p[ , s] )]

p (precision) The maximum total number of decimal digits that will
be stored, both to the left and to the right of the decimal point. The
precision must be a value from 1 through the maximum precision of 38.
The default precision is 18.

s (scale) The number of decimal digits that will be stored to the
right of the decimal point. This number is subtracted from p to
determine the maximum number of digits to the left of the decimal
point. The maximum number of decimal digits that can be stored to the
right of the decimal point. Scale must be a value from 0 through p.
Scale can be specified only if precision is specified. The default
scale is 0; therefore, 0 <= s <= p.

I highlighted the relevant phrase.

When you specify the same value (38) for p and s you don't leave room for any digits before the decimal point.

ColdFusion Error converting data type decimal to decimal

It turns out that the value being passed to the stored procedure was too large for the data type of the column into which it was being inserted. Data type was Decimal(3,2) and the value I was passing had more then three digits.

The solution was to check the size of the number being passed to the stored procedure, and give the user an error if the value is too large.

Thanks to @Tomalak for the help!

Assigning values to Null throwing error on decimal type

ISNULL(check_expression, replacement_value) returns the same type as check_expression (ref). In your examples it is attempting to convert varchar '' back to the original datatype:

SELECT CAST('' AS FLOAT)         -- 0
SELECT CAST('' AS INT)           -- 0
SELECT CAST('' AS REAL)          -- 0
SELECT CAST('' AS DATE)          -- 1900-01-01
SELECT CAST('' AS DECIMAL(19,6)) -- Error converting data type varchar to numeric.

The behavior of last example is documented here:

SQL Server also returns an error when an empty string (" ") is
converted to numeric or decimal.

VARCHAR to DECIMAL

Implemented using Custom Function.
This will check whether the string value can be converted to Decimal safely

CREATE FUNCTION [dbo].[TryParseAsDecimal]
(
    @Value      NVARCHAR(4000)
    ,@Precision INT
    ,@Scale     INT
)

RETURNS BIT
AS
BEGIN

    IF(ISNUMERIC(@Value) =0) BEGIN
        RETURN CAST(0 AS BIT)
    END
    SELECT @Value = REPLACE(@Value,',','') --Removes the comma

    --This function validates only the first part eg '1234567.8901111111'
    --It validates only the values before the '.' ie '1234567.'
    DECLARE @Index          INT
    DECLARE @Part1Length    INT 
    DECLARE @Part1          VARCHAR(4000)   

    SELECT @Index = CHARINDEX('.', @Value, 0)
    IF (@Index>0) BEGIN
        --If decimal places, extract the left part only and cast it to avoid leading zeros (eg.'0000000001' => '1')
        SELECT @Part1 =LEFT(@Value, @Index-1);
        SELECT @Part1=SUBSTRING(@Part1, PATINDEX('%[^0]%', @Part1+'.'), LEN(@Part1));
        SELECT @Part1Length = LEN(@Part1);
    END
    ELSE BEGIN
        SELECT @Part1 =CAST(@Value AS DECIMAL);
        SELECT @Part1Length= LEN(@Part1)
    END 

    IF (@Part1Length > (@Precision-@Scale)) BEGIN
        RETURN CAST(0 AS BIT)
    END

    RETURN CAST(1 AS BIT)

END

Conversion failed when converting the varchar to numeric and displaying numeric values only

I strongly recommend case instead of iif(), but it is the same problem. The expression refers to a single value, with a single type. If one branch ("then") is a number and the other a string ("else"), then the result is a number.

So, convert the number to a string:

select t3.*,
       (case when DAYSS >= 1 then cast(DAYSS as varchar(255))
             else 'Out of Stock'
        end) as NEWDA
from TABLE3 t3;

When a VARCHAR contains a Decimal, you can't convert it to an INT -- Why?

Basically, SQL Server is unsure of which type of conversion you are attempting...

You can do an implicit conversion to int by assigning your variable directly:

SET @intcode = @Code -- This will Truncate the decimal portion of the number

Alternatively, you can do an explicit conversion in SQL Server 2008 using TRY_CONVERT:

Like this:

select @intCode = convert( int, try_convert( decimal, @code )  )

Either way, you're not doing the string manipulation.

Here's a great explanation of implicit vs explicit conversion on Simple Talk:
here

Cheers!

---

Related Topics