Q

SQL to round up to nearest thousand

SQL expert Rudy Limeback explains how to use the CEILING function.

In SQL Server 2005, I want the next nearest thousand number. For example, if I gave 460 the output is 1000, if 1000 output 1000, if 1001 output 2000, if 1550 output 2000, if 2001 output 3000. Can you help me?
This can accomplished with the SQL Server CEILING function.

select mynumber
     , mynumber/1000.0               as dividedby1000
     , ceiling(mynumber/1000.0)      as ceilingresult
     , ceiling(mynumber/1000.0)*1000 as myfinalnumber
  from mynumbers

CEILING returns the smallest integer which is equal to or greater than the number it is given. Notice that we need a complex expression involving CEILING, in order to get the "rounded up to next 1000" number that you wanted. The "trick" (if you want to call it that) is to divide by 1000.0, which forces a decimal result, before applying the CEILING.

The query above also illustrates how to go about testing a complex expression like this. Build it up, one step at a time, in separate columns.

The results of the query on the data you provided are:

mynumber dividedby1000 ceilingresult myfinalnumber
   460      0.460000         1           1000
  1000      1.000000         1           1000
  1001      1.001000         2           2000
  1550      1.550000         2           2000
  2001      2.001000         3           3000

Neat, eh?

This was first published in March 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close