Questions tagged [decimal]
The decimal tag has no usage guidance.
27
questions
0
votes
2
answers
67
views
What type should I use to store values like "999999.99" in Cassandra?
I wanna add a price column to my table to be able to store values from 0.0 to 999999.99. I tried price DECIMAL(8,2) but it seems it doesn't work. How can I store such a value?
I am also wondering to ...
- 131
2
votes
1
answer
806
views
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
...
- 181
1
vote
1
answer
515
views
How to store decimal numbers in MS Access
I tried to store decimal number, but couldn't. It's Office 2019. I tried both number types, and different "format". One format had some zero's show up after a comma, but could not enter ...
0
votes
1
answer
43
views
Changing data type of an existing column of SQL database
One of the field in an existing SQL table is Decimal (5,5) with no foreign key relationships. Developers wants to change it to Decimal (7,5).
What will be the downside of making such changes wich has ...
- 1,039
0
votes
1
answer
1k
views
Displaying number before decimal points in oracle
I have a logic where I want to calculate and display numbers based upon some operations. The operations are below
V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100),
So I want to calculate ...
- 203
2
votes
3
answers
7k
views
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
...
- 139
1
vote
1
answer
3k
views
Mongo: how to covert a string to decimal?
I have written millions of documents with $CURRENT_CASH_BALANCE as strings instead of decimals. How can I convert them all to decimals?
- 11
2
votes
2
answers
2k
views
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. ...
- 53
3
votes
1
answer
305
views
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 ...
- 223
5
votes
1
answer
462
views
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 ...
- 53
5
votes
3
answers
667
views
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 ...
- 209
10
votes
2
answers
2k
views
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'...
- 103
0
votes
1
answer
29
views
Having values or some state in a database
Suppose I have a table with several price values in it
ID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null
Each price value can be ...
- 103
0
votes
1
answer
521
views
Average Value not working
I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I ...
- 151
0
votes
2
answers
751
views
How to store bitcoin value and other currencies values together in MySql database?
I need to store bitcoin value and other currencies values together , I have a decimal column (16,8) for amounts. It behaves normally for bitcoin values , but when I want to store other currencies like ...
- 3
6
votes
2
answers
1k
views
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 ...
- 171
1
vote
1
answer
3k
views
MySQL bug - decimal comparison
I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in.
Somehow, MySQL considers that "14.975"...
3
votes
1
answer
3k
views
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(...
- 3,771
18
votes
3
answers
140k
views
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],',',...
- 521
0
votes
2
answers
3k
views
Mysql Data truncation for Valid DECIMAL
create table test(id INT, value DECIMAL(25,6));
insert into test values (16, 10e16);
insert into test values (17, 10e17); -- Works till here
insert into test values (18, 10e18); -- Fails here. Data ...
- 103
0
votes
1
answer
1k
views
MySQL - Get actual precision and scale of number
I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, ...
- 329
0
votes
1
answer
184
views
Calculations involving thresholds between two tables
I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is ...
- 153
1
vote
0
answers
262
views
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 ...
- 111
8
votes
5
answers
15k
views
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) = ...
- 83
3
votes
1
answer
1k
views
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 ...
13
votes
3
answers
64k
views
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 ...
- 1,813
4
votes
1
answer
175
views
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 ...
- 185