After testing I found that it was not the decimal place that was causing the problem, it was the precision (10)

This doesn't work: Arithmetic overflow error converting varchar to data type numeric.

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(10, 4))

This worked

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(13, 4))

Should be like 9 int + 4 floating = 13 chars

If you don't mind rounding, you can do what you want in one step:

ALTER TABLE dbo.Stringy 
    ALTER COLUMN latitude decimal (9, 6) 
    WITH (ONLINE = ON); -- SQL Server 2016 onward

This can be an online operation in SQL Server 2016 or later.

If you need to truncate (not round) things are more tricky. You would need to perform a calculation like:

SELECT 
    CONVERT
    (
        decimal(9,6), 
        ROUND
        (
            CONVERT
            (
                decimal(10, 7), 
                S.latitude
            ), 
            6, -- decimal places
            1  -- truncate, don't round
        )
    )
FROM dbo.Stringy AS S;

If there might be some non-numeric values in there, use TRY_CONVERT instead and accept nulls in the new column, or provide a default value with ISNULL.

If the objective is to save space, you'll need to rebuild the table or run DBCC CLEANTABLE (perhaps in small batches until complete) to achieve that. The dropped variable-length string column will continue to take up space until then.

Hello friends,

I have such a situation. I need to change varchar data type into decimal.

CONVERT (VARCHAR, SUM(b.[HistoryQuantity]) / SUM(a.[TOTFCST]) * 100) + ‘%’ AS ‘Bias’

This is how my bias looks like right now. p.s. The only reason why i wrote Convert Varchar is because I was getting an error without CONVERT(VARCHAR) namely ‘Error converting data type varchar to numeric’.

Does someone know how I can make my bias just a number with two decimal points. Like regular percentage (for ex. 5.22%).

The issue in the above query is only due to these value (7,956.00 and 5,328.00 ) when casting in implemented on these values the value returned as 7.00 and 5.00 due to which the query gives the result as 2569.00.

Just remove these commas from these numbers and the query will execute properly

SELECT SUM(REPLACE(Amount, ',', '')) as result FROM tblserviceinvoice