Does T-SQL have a way of truncating real and float values?

  • 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.

  • Look at ROUND(), particularly the last parameter.

  • 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.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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.

    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.

  • PhilPacha wrote:

    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.

    • This reply was modified 1 month, 1 week ago by  Rod at work.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • 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.

     

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod at work wrote:

    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.

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply