SQL SERVER DATATYPE Implict Conversion

Original Path

Another good Path

Data Types (Transact-SQL)

The data type DECIMAL and NUMERIC contains scalar value.
So when convert to this type we have to do an explicit convert
and then cast to the type DECIMAL or NUMERIC.

eg.

DECLARE @myval decimal (5, 2)

SET @myval = 193.57

PRINT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))

Things to be Noted

DECLARE @val INTEGER

SET @val = 1000

PRINT CAST(1000 AS DECIMAL(5,1))
PRINT CAST(1000 AS DECIMAL(4,1))

--IF the PRECISION should be greater than or equal to the size of the
--value after subtracting the scalar value from the precision.
--DECIMAL(Fixed PRECISION, scale numbers) Otherwise this error occurs.
--Arithmetic overflow error converting VARCHAR to data type NUMERIC.

DECLARE @test AS INT
SET @test = 10000000

--PRINT CAST(@test AS DATETIME)
--The above commented query gives the error
--Arithmetic overflow error converting expression TO data type datetime.
--This TIME we have TO USE CONVERT AND NOT cast.

PRINT CONVERT(CHAR(8), DATEADD(second, @test, ''), 114)