I'm working on a new app and database, which has real data types in some of the tables. Due to some regulations we must store data in those fields only to a certain significant digit. For example, if the value calculated is 2.159 and the regulation says we're to only keep two decimal points we must save it as 2.15, not 2.16. I've handled this in the C# app I'm writing, but now I've got to go back to address tens of thousands of records, which for whatever reason haven't saved the data truncated as it should.
The old app was a Microsoft Access app. I've never seen it nor the .MDB file so I've no idea how it handled those data elements in its table(s) or how it presented it in the MS Access forms and reports.
Anyway, I'd like to know if there's some built-in function in T-SQL that will truncate real and float data types to no more than a specific number of significant digits?
Kindest Regards, Rod Connect with me on LinkedIn.
October 23, 2020 at 2:45 am
Look at ROUND(), particularly the last parameter.
October 23, 2020 at 3:18 am
1st, as it's said, round() with the parameter,
2nd, FLOOR(),
3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL or FLOAT.
_____________
Code for TallyGenerator
If you CAST the value to a different data type, such as decimal, SQL will automatically round the value for that type, so you will have to do a ROUND first in order to truncate the value.
;WITH cte_float_value AS (
SELECT CAST(2.159 AS float) AS float_value
)
SELECT CAST(ROUND(float_value, 2, 1) AS decimal(9, 2)) AS new_value
FROM cte_float_value
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 23, 2020 at 3:23 pm
1st, as it's said, round() with the parameter,
2nd, FLOOR(),
3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL or FLOAT.
I had not thought of the data type used. You've got a good point. I'm not the one who designs the database, but I'll argument that we should use DECIMAL instead.
Kindest Regards, Rod Connect with me on LinkedIn.
October 23, 2020 at 8:26 pm
Look at ROUND(), particularly the last parameter.
At first, I didn't think this would work, but then I discovered that the ROUND() function can have three parameters. So, this does work.
Kindest Regards, Rod Connect with me on LinkedIn.
October 23, 2020 at 8:29 pm
1st, as it's said, round() with the parameter,
2nd, FLOOR(),
3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL or FLOAT.
Unfortunately, this also rounds rather than truncates. I tried entering this:
DECLARE @bozo FLOAT = 3.14159;
DECLARE @bozoDecimal DECIMAL(9,4);
SET @bozoDecimal = @bozo;
SELECT @bozoDecimal;
But the SELECT resulted in 3.1416, whereas I need 3.1415.
Kindest Regards, Rod Connect with me on LinkedIn.
October 24, 2020 at 3:59 am
Sergiy wrote:1st, as it's said, round() with the parameter,
2nd, FLOOR(),
3rd, if you store numbers with fixed decimal places you should be using DECIMAL data type for that, not REAL or FLOAT.
Unfortunately, this also rounds rather than truncates. I tried entering this:
DECLARE @bozo FLOAT = 3.14159;
DECLARE @bozoDecimal DECIMAL(9,4);
SET @bozoDecimal = @bozo;
SELECT @bozoDecimal;But the SELECT resulted in 3.1416, whereas I need 3.1415.
No, you've got me wrong.
Changing data type does not eliminate the necessity of appropriate rounding.
You still need to use FLOOR or ROUND(..., ..., 1)
The item 3 in my list was about storing the results of the rounding. If you store it in columns with FLOAT or REAL data types some values might appear not exact decimals.
So, once you've got your rounded values keep them decimal.
_____________
Code for TallyGenerator
November 9, 2020 at 10:55 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply