Rounding in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rounding in SQL

Author

Topic

nod
Starting Member

19 Posts

Posted - 2004-10-13 : 18:14:03

How do you keep sql server from rounding? 5.9 > 6 and so on

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-13 : 18:14:54

Use the correct data type?

Tara

vandalesm
Starting Member

8 Posts

Posted - 2004-10-13 : 21:26:20

select round(5.9,0)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-13 : 21:28:12

Ummmm, he's trying to PREVENT it from rounding.

vandalesm
Starting Member

8 Posts

Posted - 2004-10-13 : 21:44:52

hey sory about that. just ignore my post.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-13 : 22:03:01

And the correct datatype (probably) would be some version of decimal in that case. Look up datatypes in SQL Server Books Online. All kinds of good info in there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 10:30:53

I used decimal data type 5,2 and it still rounded to 60 from 5.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 12:13:14

You must be doing something wrong:

DECLARE @num decimal(5,2)

SET @num = 5.9

PRINT @num

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-14 : 12:51:24

It LOOKS like and is displaying as 6.0, or it is being stored as 6.0 ? What happens when you type

SELECT YourCol
FROM YourTable

in Query Analyzer? What results are displayed?

- Jeff

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 15:35:30

It was the temp table definition which was defined as decimal without the precision.

So that is fixed. HOw about this one..any ideas..select datediff(hh, '2004-10-01 10:20AM', '2004-10-01 10:40AM') how could I get something like '.' something...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 15:47:04

What do you mean how could you get something like "."? I correctly get 0 returned.

Tara

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 16:34:14

Right but we know that there is a twenty minute difference in that time frame so by using minute vice hour you see this, though I wanted to know if there was a way to do a datediff for say 12:00 - 1:30 and get 1.5 hours returned or the same query and get .2 hours vice the 0. Through variables this is very possible but is there a built in functrion that will allow for the results so datediff(hh, '2004-10-01 10:20AM', '2004-10-01 10:40AM') would yield .23 or or some decimal instead of 0. We know that it isn't an hour but what percent of the hour is it? That's what I wiould like to find out. Sounds like I'd need to build a function to do this for me...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-14 : 16:38:10

if the number of hours is not good for you, can you think of any other time units that you can get the difference in? What unit is an hour made up of? What is a way to "break down" an hour?

Have you figured it out, yet?

- Jeff

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 16:45:41

Obviously minutes, though then if the minutes were > than 60 I'd have to increment the hour, which would further complex the issue..I'm wondering is there an easier way to accomplish it? Is there?

Am I simply complicating the issue myself?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 16:47:30

Can't your front end handle the conversion for you? So if you use minutes and get 90 returned, can't you display that as 1.5 hours at the presentation layer?

Tara

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 16:50:25

How about we take your own code and ask it for the difference in minutes not time.

select datediff(hh, '2004-10-01 10:20AM', '2004-10-01 10:40AM')

select datediff(mi, '2004-10-01 10:20AM', '2004-10-01 10:40AM')

That will return your 20 minute difference.

-Jon
Now a "Yak Posting Veteran".

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-14 : 16:55:04

let's do some math together.

1 hour = 60 minutes.

To convert X minutes to hours, divide X by 60 and throw away the remainder.

For example, 90 minutes divided by 60 = 1
130 minutes divided by 60 = 2

Now, what about the remainder? There is a mathematical operator called MOD that returns the remainder of the division between two integers. Some examples:

65 MOD 30 = 5
135 MOD 60 = 15
120 MOD 60 = 0

That is how we can get the minutes left over ! SQL Server represents MOD with %.

so if you have 130 minutes:

130 / 60 = 2
130 % 60 = 10

which equals 2 hours and 10 minutes.

Does this help?

- Jeff

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 17:01:19

Even more...


DECLARE @startime datetime
DECLARE @endtime datetime 

select @startime = '2004-10-01 10:20AM', @endtime = '2004-10-02 10:40AM'

select datediff(mi, @startime, @endtime)/60.0

-Jon
Now a "Yak Posting Veteran".

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 17:34:10

select convert(varchar(2), datediff(mi, '2004-10-01 10:20AM', '2004-10-01 11:50AM') / 60) + 'hr ' + convert(varchar(2), datediff(mi, '2004-10-01 10:20AM', '2004-10-01 11:56AM') % 60) + 'min'

The problem with using variables is that they cannot be used outside of a function in data retrieval that I'm aware of anyway..which restricts me to bare bones sql and any functions whether created by me or preferably functions that already exist...

No the UI is rarely ever used for anything complex...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-14 : 17:49:51

the variables are an example for "proof of concept". he is just demonstrating how to write your formula, so you can cut and paste into Query Analyzer and see for yourself. Something you should be doing as well as you try to solve this problem.

So have you gotten your answer ?

- Jeff

nod
Starting Member

19 Posts

Posted - 2004-10-14 : 18:31:22

The fix was using the modulo operator to get the remainder never thought about it before..so select convert(varchar(2), datediff(mi, '2004-10-01 10:20AM', '2004-10-01 12:56pM') / 60) + 'hr ' + convert(varchar(2), datediff(mi, '2004-10-01 10:20AM', '2004-10-01 12:15pM') % 60) + 'min' gives me the exact minutes.

Do you see any issues with it?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources