Why Round or Truncate functions does'n work?

Question

4.00/5 (1 vote)

See more:

Hi everyone,
i have a problem truncating or rounding data in SQL.
If i round or truncate data like this: Round(1.456, 2) or Round(1.456, 0) or Truncate(1.456, 2)... - everything is fine.

BUT
lets say i have two SQL tables one for units of measurement and the other one is store.

measurement table values

SQL

|id  |measure   |show_decimals (INT(1))|
----------------------------------------------
|1  |unit          |0                                  |
|2  |kilo          |3                                  |
|3  |meter       |2                                  |

store table values

SQL

|id  |item       |measurement_id   |count_left_in_store (DECIMAL(16,6))                |
----------------------------------------------------------------------------------------------
|1  |pencil     |1                         |987.000000  (its just simply 987 units of pencils) |

SO
if i am using sql query like this:

SQL

SELECT TRUNCATE(s.count_left_in_store, m.show_decimals) as UnitsLeft
FROM store s
JOIN  measurement m on m.id = s.measurement_id
WHERE s.id = 1;

it still gives me 987.000000 UnitsLeft instead of just 987 UnitsLeft.
I tried Truncate, Round, Floor functions - nothing still the same.
Everything works fine then i write Truncate(987.000000, 0) or Truncate(s.count_left_in_store, 0), but doesn't work with TRUNCATE(s.count_left_in_store, m.show_decimals)

Why i can't use other table column value as a decimal parameter in Truncate, Round, Floor and other math functions?

Can anyone help me out right there?

Posted 27-Oct-14 19:45pm

evacka

Updated 27-Oct-14 21:07pm

v8

Add a Solution

Comments

RossMW 28-Oct-14 1:52am

Not near a computer to check but the data type is decimal(16,6), so will be represented as such. You probably just need to convert to numeric(16,0)

evacka 28-Oct-14 2:32am

i don't think problem is decimal(16,6), because i tried to Truncate (Round) Truncate(s.count_left_in_store, 0) which is Truncate([decimal(16,6)], D) ant it works fine. Problem is D - decimal value of Truncate function. I just cant use m.show_decimals value from table measurement which is simple INT (and there i tried other possible options, but still nothing)

3 solutions

Solution 2

Try This

SQL

DECLARE @val Decimal(10,6) = 987.120000,@DecPrecision int
set @DecPrecision=0
select Cast(@val as Float)
select ROUND(Convert(Float,@val),@DecPrecision)

Even Maciej Los was trying to make you understand the same thing.

Posted 27-Oct-14 22:34pm

Shweta N Mishra

Comments

evacka 28-Oct-14 4:51am

Is that even correct SQL syntax? No one of you code line worked for me. I have never seen that in Convert function first goes type parameter and just then value like this Convert(Float, val). I know just that syntax like that Convert(val, decimal(10,0)); By the way i cant convert to INT and either to FLOAT. MySql is telling me that it is syntax error.

evacka 28-Oct-14 4:57am

Could you try to write real sql query without DECLARE and @ stuff. Just using my specified tables (measurement, store) and columns?

Shweta N Mishra 28-Oct-14 5:28am

This works well in SQL Server. Thast the way its written in SQL Server .You should be able to convert it syntactically as per MySQL version.

And same thing you can apply to your specified tables. Did you tried doing that ?
You have to convert it another datatype otherwise Decimal datatype will behave as it is doing now.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-10-27T21:14:00

Test it

SQL

DECLARE @val DECIMAL(10,6) = 987.000000
SELECT CONVERT(INT, @val) AS Intgr,  CONVERT(DECIMAL(10,2), @val) Dec2,  CONVERT(DECIMAL(10,0), @val) Dec0

and find out why... Just kidding ;)

You should convert to the destination data type.

Have a look here: ROUND (sql)[^]
Round function is working but returns the same data type ;)

[EDIT]

In your case i suggest to create stored procedure like this (using dynamic query):

SQL

CREATE PROCEDURE GetStoreArticle
    @id INT
AS
BEGIN

DECLARE @dec INT
SELECT @dec = show_decimals FROM measures AS m INNER JOIN store AS s ON m.ID = s.measurement_id  WHERE s.id = @id

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT s.id, s.item,  CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) +  '), s.count_left_in_store) AS UnitsLeft
    FROM store AS s INNER JOIN measures AS m ON s.measurement_id = m.ID
    WHERE s.id = ' + CONVERT(VARCHAR(10),@id)

EXEC (@sql)

END

Sample code used for test:

SQL

CREATE TABLE #measures(ID INT IDENTITY(1,1), measure VARCHAR(30), show_decimals INT)

INSERT INTO #measures (measure, show_decimals)
VALUES('unit',0), ('kilo',3), ('meter', 2)

CREATE TABLE #store (id INT IDENTITY(1,1), item VARCHAR(30), measurement_id INT, count_left_in_store DECIMAL(16,6))
INSERT INTO #store (item,measurement_id,count_left_in_store )
VALUES('pencil',1 , 987), ('sugar', 2, 5879), ('UDP cable', 3, 22)


DECLARE @id INT = 3

DECLARE @dec INT
SELECT @dec = show_decimals FROM #measures AS m INNER JOIN #store AS s ON m.ID = s.measurement_id  WHERE s.id = @id

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT s.id, s.item,  CONVERT(DECIMAL(10, ' + CONVERT(VARCHAR(10),@dec) +  '), s.count_left_in_store) AS UnitsLeft
    FROM #store AS s INNER JOIN #measures AS m ON s.measurement_id = m.ID
    WHERE s.id = ' + CONVERT(VARCHAR(10),@id)

EXEC (@sql)

DROP TABLE #store
DROP TABLE #measures

It works as well ;)

evacka · Accepted Answer · 2014-10-27T23:24:00

i found a solution,

instead of

SQL

SELECT TRUNCATE(s.count_left_in_store, m.show_decimals) as UnitsLeft
FROM store s
JOIN  measurement m on m.id = s.measurement_id
WHERE s.id = 1;

i used

SQL

SELECT TRUNCATE_TO(s.count_left_in_store, m.show_decimals) as UnitsLeft
FROM store s
JOIN  measurement m on m.id = s.measurement_id
WHERE s.id = 1;

where TRUNCATE_TO is my own created function in sql which is

SQL

FUNCTION TRUNCATE_TO(
  nmbr DECIMAL(16,6),
  dec INT(1) UNSIGNED
) RETURNS CHAR(16)
BEGIN
  DECLARE R CHAR(16) DEFAULT '0';

  SET R = CONVERT(TRUNCATE(nmbr, dec), CHAR(16));

  RETURN R;
END

Thanks Maciej Los for advice to use a stored procedures ;)

Why Round or Truncate functions does'n work?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0