Questions tagged [decimal]
27 questions
18
votes
3 answers
Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)
I have an SQL table of varchar columns which contain Greek formatted numbers (. as thousand separator and comma as decimal separator)
The classic conversion
CONVERT(numeric(10,2),REPLACE([value],',','.'))
does not work because the . (thousand…
PanosPlat
- 521
- 1
- 6
- 14
13
votes
3 answers
Automatic decimal rounding issue
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts.
For example, let's…
Kahn
- 1,803
- 2
- 20
- 28
10
votes
2 answers
Problem with union casting integer to ceiling(decimal)
I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.
The problem is that this query is generated by an external library, so I don't have control over this code, at this level at…
ngcbassman
- 103
- 5
8
votes
5 answers
How does SQL Server determine precision / scale?
I'm running SQL Server 2012
SELECT
0.15 * 30 / 360,
0.15 / 360 * 30
Results:
0.012500,
0.012480
This one is even mor confusing to me:
DECLARE @N INT = 360
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360
DECLARE @C DECIMAL(38,26) =…
cpacheco
- 83
- 1
- 1
- 5
6
votes
2 answers
Why is datalength of decimal 5 regardless of precision?
I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on the decimal's precision. So I tried to test it…
xr280xr
- 171
- 5
5
votes
1 answer
Question regarding decimal arithmetic
I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while the decimal(18, 14) rounds to 19. My understanding…
Mythikos
- 53
- 4
5
votes
3 answers
Storing two integers as a decimal
What are the downsides to storing two integers as a decimal?
I am storing asset details in tables, each asset type has its own table (each asset is very different) and using another table to define the asset tables, so each asset table has an…
mike16889
- 209
- 1
- 8
4
votes
1 answer
According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?
Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3?
What about DECIMAL 3,3?
Does this mean that the precision is a floating decimal place or static that must have 3 decimal places even if they are all zero?
jcalfee314
- 185
- 1
- 6
3
votes
1 answer
Why doesn't `1` fit into a decimal(4, 4) column?
I have a decimal(4, 4) column in MS SQL Server 2008 R2. If I understand correctly, that means:
Precision of 4, ie up to four digits can be stored after the decimal place
Scale of 4, ie a a total of four digits can be stored
When I run an update…
just.another.programmer
- 263
- 1
- 3
- 12
3
votes
1 answer
What's the smartest way to find the scale of an unrestricted numeric type?
By laziness and convenience I integrated quite a lot of data from a partner in an unrestricted numeric type in my PostgreSQL database.
However it now seems that depending of different shipments from this partner the scale of the numeric varies from…
MarHoff
- 253
- 2
- 9
3
votes
1 answer
How are precision and scale calculated when decimals are multiplied?
When decimals are divided, the precision and the scale of the result value are calculated due to this formula:
max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38
max scale = MAX(6, s1 + p2 + 1)
What's the formula when decimals are…
gotqn
- 4,348
- 11
- 52
- 91
2
votes
1 answer
Converting varchar to decimal with truncate
I have a column called latitude which is currently varchar(20)
I want to convert it to DECIMAL(9,6)
However the data stored inside the column is greater than 6 decimal points
i.e.
48.123456891123456
the table in question has over 50 billion rows and…
Quade
- 321
- 1
- 3
- 12
2
votes
3 answers
How to prevent PostgreSQL from automatically rounding numeric types?
I have a simple schema and query for a MWE.
Schema:
CREATE TABLE ttable (
tcol Numeric(19,4)
)
Query:
INSERT INTO ttable (tcol) VALUES ('123.45678');
SELECT * FROM ttable;
Fiddle
Result:
123.4568
In this case, I have lost precision. I entered…
TechnoSam
- 139
- 1
- 1
- 5
2
votes
2 answers
How to count decimals without zeros
How can I count all decimal place up to the first zero
For example I have a column named x with an entry of numeric datatype value 1.955000000
I want to count the decimal places without the zeros. So here I want the query to make an output of 3 not…
DataLordDev
- 63
- 1
- 7
1
vote
0 answers
Migration error: "Invalid date literal detected" when reading decimal
I'm attempting a migration from MSSQL to MySQL and keep coming across a "Invalid date literal detected" error when reading certain decimal columns. I'm running the following (all up-to-date as of posting):
Windows 7
MSSQL 2008 R2
MySQL 5.6.21…
Ryan Gillies
- 111
- 1