I am attempting to generate a table containing values of 10^(existing_field_value / 10). I can recreate the problem with the following simplified table:
Date, Time, Timer, Overall_Spectra, 6_3_Hz, 20_Hz
10/23/2018, 12:24:13, 0:19:59, 69.7, -17.4, 8.9
10/23/2018, 12:24:14, 0:19:58, 70.8, -31.1, 4.4
10/23/2018, 12:24:15, 0:19:57, 70.7, -28.9, 4.8
10/23/2018, 12:24:16, 0:19:56, 69.0, -27.0, 5.9
The data was imported from a flat text file with the following data types:
Date, date
Time, time(0)
Timer, time(0)
Overall_Spectra, decimal(3,1)
6_3_Hz, decimal(3,1)
20_Hz, decimal(3,1)
Using the T-SQL statement below, I was able to comment out 2 of the 3 referenced fields at a time. Doing so, the desired results are returned for the 6_3_Hz and 20_Hz fields, but for the Overall_Spectra field SSMS returns:
select
POWER(10.000000000000000000000000000000000000,Overall_Spectra/10)
POWER(10.000000000000000000000000000000000000,"6_3_Hz"/10)
Power(10.000000000000000000000000000000000000,"20_Hz"/10)
from sound;
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
I've referenced the following MS documentation in an attempt to understand what's going on (specifically the Return Types table,) but to no avail:
https://learn.microsoft.com/en-us/sql/t-sql/functions/power-transact-sql?view=sql-server-2017
I'm confused about the point at which SQL employs the float data type, as that seems to be in some way the cause of the error. Any insight into what SQL is doing, or what bone-headed thing I'm doing, would be greatly appreciated!!
The maximum PRECISION on a DECIMAL at least in SQL Server 2014 is 38. You are using POWER(10), any number which the POWER produces a number with more than 38 digits will cause the overflow.
So for instance this: POWER(10.000000000000000000000000000000000000, 19.9 / 10)
Will work as the number produced has <= 38 digits
If you up the number to POWER(10.000000000000000000000000000000000000, 20.9 / 10) you get a number with more than 38 digits and then the overflow error
Please see this as reference to Precision and Scale: