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.
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.
