SQL 2000 rounding numbers up/down

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

Transact-SQL (2000)

SQL 2000 rounding numbers up/down

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-04 : 16:15:20

If I have a query that produces the following results:

1.671916666
15.002333333
14.998816666
15.012566666
14.993250000

whats the best way to round those to the nearest whole number?

I tried round (login, -1) but I need the numbers to be rounded to the nearest number, like 1.6 should be 2, 14.9988 should be 15, etc.

Thank you

Doug

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-04 : 16:20:44

Try ROUND(login, 0).

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-04 : 17:38:07

Thanks Rob.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-05 : 13:31:44

Rob,

What about if I want to convert to the nearest minute? The numbers to the left of the decimal are hours.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 13:35:06

Doug, could you provide a data example of what it would show if converted to the nearest second?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-05 : 14:42:04

Tara,

I can but it may take me a while to do so. The query that I have is calculating the day like this:

dateadd(n,Timestamp, '12/31/1899')-([LoggedIn]/1000)/60/1440 +1 as date

Does that help at all?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 14:45:46

It doesn't help, but maybe it helps other. I need to see sample output in order to come up with a solution. Use the data that you've got above in your original post and let us know what the amount should be once converted to the nearest second.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-05 : 16:24:50

quote:
Originally posted by dougancil

Tara,

I can but it may take me a while to do so. The query that I have is calculating the day like this:

dateadd(n,Timestamp, '12/31/1899')-([LoggedIn]/1000)/60/1440 +1 as date

Does that help at all?

How could that help?

Your question is about rounding a number, but that produces a date.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources