Search code examples
sqlroundingfloorceil

Special Rounding


How can I round price value to the desired results, when use round it gives me different results and also ceiling is different !

select ceiling(price)

 -------------------------------------------------------------------------
 | test following numbers as inputted price value | Is my desired Output |
 |             INPUT                              |   OUTPUT             |
 -------------------------------------------------------------------------
 |  879999.51357924604783137                      |  880000              |
 |  879999.50720242608036391                      |  880000              |
 |  879999.47819604919865821                      |  880000              |
 |  879999.49455676516329704                      |  880000              |
 |  880000.5                                      |  880000              |
 -------------------------------------------------------------------------

Solution

  • Ceiling returns the integer equal to or higher than the value

    SELECT ceiling(879999.51357924604783137)
    

    round(X,N) rounds to N digits:

    N > 0: round to N digits to the right of the decimal point.

    N = 0: round to the nearest integer.

    N < 0: round to N digits to the left of the decimal point.

    then

     select cast(round(879999.51357924604783137,-1) as decimal(18,0))
     select cast(round( 880000.5,-1) as decimal(18,0)) 
    

    enter image description here