Update the numeric in Decimal

Mike 341 Reputation points
2022-03-10T16:17:17.447+00:00

Hello, I have to replace the number in decimal area with Zero if decimal value is less than 0.99

Data:
7889.00008000
7828.00800000
7363.00000060
7363.99999990

Expected output: --All decimals should be Zero except if decimal value is less than 0.99
7889.00000000
7828.00000000
7363.00000000
7363.99999990

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2022-03-10T17:43:37.15+00:00
    Declare @Sample Table(Nbr decimal(15,8));  
    Insert @Sample(Nbr) Values  
    (7889.00008000),  
    (7828.00800000),  
    (7363.00000060),  
    (7363.99999990);  
      
    Update @Sample  
    Set Nbr = Floor(Nbr)  
    Where Floor((Nbr*100)) % 100 <> 99;  
      
    Select Nbr From @Sample;  
    

    Tom

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2022-03-10T16:25:30.89+00:00

    Did you try to CAST the numeric to decimal?

    DECLARE @n numeric = 7363.00000060;
    SELECT CAST(@n AS decimal(20, 8));