how to round of in sql

siva nathan

9y986Learn iOS Programming

I have a table column values like below

Hours

1.3000

0.4500

3.4500

2.4000

my expected result is

Hours

how to achieve this output without using ceiling because ceiling convert 1.10 as 2 .my case is whenever the value is like .30 or 1.30 means convert 1 and 2 how to get this

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Kumar9y ago
Hi Nitin Sontakke,
I am agree with you.
Nitin Sontakke9y ago
@Amit Kumar, Vivek Kumar and Amit Gupta,

Siva aiming to use round function which is written for decimal system to round of hours and minutes. This is not possible. In hours and minutes 60 = 100 and 30 = 50. How language functions written for decimal system are expected to know that?

In some of his previous posts, I tried to explain to him that these are TWO ENTIRELY DIFFERENT things and there is no native support from language.
Amit Kumar9y ago
Hi Dear,
Use simple sql function celling then you will get exact result.

SELECT CEILING(1.30);
SELECT CEILING(0.4500);
SELECT CEILING(3.4600);
SELECT CEILING(2.4000);
Rafnas T P9y ago
hi siva, have you checked my solution
Rafnas T P9y ago
I have already given for your refernce see below
siva nathan9y ago
Hi Rafnas,
can you pls tell me the answer again ,im forgot to use
Rafnas T P9y ago
for your reference,
create function as below

private static string RoundOf(string _Value)
       {
           string[] _Valuesplit = Convert.ToDouble(_Value).ToString("F2").Split('.');
           int _intValue2 = Convert.ToInt16(_Valuesplit[1][0].ToString());
           if (_intValue2 >= 3)
           {
               _Value = Math.Ceiling(Convert.ToDouble(_Value)).ToString();
           }
           else
           {
               _Value = Math.Floor(Convert.ToDouble(_Value)).ToString();
           }

           return _Value;
       }

and pass your value as string to this function
for example,

string _Value = "2.30";
  _Value = RoundOf(_Value);     //ex1

string _Value = "2.15";
  _Value = RoundOf(_Value);  //ex2
try this, I have already tried this is working fine
Rafnas T P9y ago
hi siva,
for this I have already give you a solution have tried that.
Amit Gupta9y ago
Your question was not clear to me. In the first para, you are expecting 1.3000 to 2, 0.4500 to 1. This can be achieved by using ceiling function.

And on the last para, you are asking .30 or 1.30 to 1 and 2. What was that !!

Can you brief the program scenario, for what purpose it was going to be put this logic.
Vivek Kumar9y ago
Hi Siva,
You can get all value using ceiling function whatever you provide in your expected result. Can you elaborate more?