Casting INT, BIGINT, SMALLINT, TINYINT to DECIMAL - a cheatsheet (SQL Server)

Type casting.. some are obvious, some are not.. When you're casting to DECIMAL(p,s) type, you've got to stop and think what scale you need and what precision is enough.

Fast replay

On MSDN: http://msdn.microsoft.com/en-us/library/ms187746.aspx (decimal and numeric in T-SQL)

In following example you have to store decimal like this 98'765.4321.

Your precision is 9: 98'765.4321

Your scale is 4: 98'765.4321.

Another approach:

Take your "max value", which is 5 digits: 98'765.4321.

Add your scale, which is 4:  98'765.4321.

And your precision is 5 + 4 = 9.

So your destination type is DECIMAL(9,4).

In case of negative number, like -98'765.4321 DON'T count the sign. Precision is still 9.

Perfect precision

Now, what is safe precision for casting BIGINT to DECIMAL?

Back to MSDN: http://msdn.microsoft.com/en-US/library/ms187745.aspx (int, bigint, smallint and tiny int in T-SQL).

BIGINT is 8-Bytes data type.

Its range is -2^63 (-9'223'372'036'854'775'808) to 2^63-1 (9'223'372'036'854'775'807).

Now, count digits above..

Or..

Better ask WolframAlpha.. http://www.wolframalpha.com/input/?i=2%5e63

So.. 2^63 is ~9.22 x 10^18 which is ~0,922 x 10^19. So required precision is 19. As BIGINT cannot have digits after coma scale is 0. Finally, required type is DECIMAL(19,0).

Fast check

DECLARE @dec19 DECIMAL(19,0)

SET @dec19 = -9223372036854775808 -- OK

SET @dec19 = 9223372036854775807 -- OK

DECLARE @dec18 DECIMAL(18,0)

SET @dec18 = -9223372036854775808 -- Error: Arithmetic overflow error converting numeric to data type numeric.

SET @dec18 = 9223372036854775807 -- Error: Arithmetic overflow error converting numeric to data type numeric.

Which means DECIMAL(19,0) is perfect for casting BIGINT and DECIMAL(18,0) is too short.

Divisibility

Did I say "perfect"?

Let's try to divide DECIMAL(19,0) variable by 1000 and store it as DECIMAL(19,3)..

(scale changed, precision is still the same)

DECLARE @dec190 DECIMAL(19,0)

DECLARE @dec193 DECIMAL(19,3)

SET @dec190 = 9223372036854775807 -- BIGINT max value

SET @dec193 = @dec190 / 1000

SELECT @dec190, @dec193

Works! Results are: 9223372036854775807 and 9223372036854775.807.

Let's have another try.. Divide BIGINT variable by 1000 and store it as DECIMAL(19,3)..

DECLARE @big BIGINT

DECLARE @dec193 DECIMAL(19,3)

SET @big = 9223372036854775807 -- BIGINT max value

SET @dec193 = @big / 1000

SELECT @big, @dec193

Is it good? No. Results are: 9223372036854775807 and 9223372036854775.000. We've lost some data.

So how to make it possible?

Like this?

SET @dec193 = CAST(@big AS DECIMAL(19,3)) / 1000

No. Arithmetic overflow error converting bigint to data type numeric.

Like this?

SET @dec193 = CAST(@big / 1000 AS DECIMAL(19,3))

No. Data after coma still lost.

We need to extend scale (and precision) of source variable to prepare it for a division.

We're about to divide by 1000, which is 10^3. So our goal is to cast source BIGINT variable to DECIMAL(19+3,0+3).. let's call WolframAlpha in here.. (joke!) ..which is DECIMAL(22,3).

We've got the winner:

DECLARE @big BIGINT

DECLARE @dec193 DECIMAL(19,3)

SET @big = 9223372036854775807 -- BIGINT max value

SET @dec193 = CAST(@big AS DECIMAL(22,3)) / 1000

SELECT @big, @dec193

or (less spectacular)..

DECLARE @big BIGINT

DECLARE @dec190 DECIMAL(19,0)

DECLARE @dec193 DECIMAL(19,3)

SET @big = 9223372036854775807 -- BIGINT max value

SET @dec190 = @big

SET @dec193 = @dec190 / 1000

SELECT @big, @dec193

..because it worked earlier in article.

Cheatsheet

And now cheatsheet is coming..

int type	max value	max value - scietific notation	cast to decimal type	cast to decimal type - divisible by 1000
BIGINT	2^63-1	~0.922 x 10^19	DECIMAL(19,0)	DECIMAL(22,3)
INT	2^31-1	~0.215 x 10^10	DECIMAL(10,0)	DECIMAL(13,3)
SMALLINT	2^15-1	32767	DECIMAL(5,0)	DECIMAL(8,3)
TINYINT	2^8-1	255	DECIMAL(3,0)	DECIMAL(6,3)

To be sure..

DECLARE @dec190 DECIMAL(19,0) =  9223372036854775807
DECLARE @dec223 DECIMAL(22,3) =  9223372036854775807.123
DECLARE @dec100 DECIMAL(10,0) =  2147483647
DECLARE @dec133 DECIMAL(13,3) =  2147483647.123
DECLARE @dec50 DECIMAL(5,0) = 32767
DECLARE @dec83 DECIMAL(8,3) = 32767.123
DECLARE @dec30 DECIMAL(3,0) = 255
DECLARE @dec63 DECIMAL(6,3) = 255.123

SELECT @dec190, @dec223, @dec100, @dec133, @dec50, @dec83, @dec30, @dec63

No errors.

That's all folks for today.