/ Code Snippets & Tips

# Teradata SQL - Round Numbers to Hundreds or Thousands

visibility 2,401 comment 0 insights toc
more_vert
##### insights Stats

The following code snippets show how to use round numbers to hundreds or thousands in Teradata.

warning Alert - The following code snippets are used to round to the smallest hundreds or thousands that is equal or greater than the input number.

### FLOOR and CEILING functions

In Teradata, there are two functions that are commonly used beside ROUND function:

• CEILING: Returns the smallest integer value that is not less than the input argument.

• FLOOR: Returns the largest integer equal to or less than the input argument.

#### Examples

```select floor(100123.23)
select ceiling(100123.23)```

The output will be 100123.00 and 100124.00 respectively.

We will use CEILING function to implement.

### Round up to hundreds using CEILING function

```select ceiling(ceiling(100123.23)/100.00)*100;
select ceiling(ceiling(100123)/100.00)*100;
select ceiling(ceiling(100103)/100.00)*100;
select ceiling(ceiling(100003)/100.00)*100;
select ceiling(ceiling(100000.001)/100.00)*100;```

The result is 100,200 for the first three statements and 100,100 for the last two statements.

### Round up to thousands using CEILING function

```select ceiling(ceiling(100123.23)/1000.000)*1000;
select ceiling(ceiling(100123)/1000.000)*1000;
select ceiling(ceiling(100003)/1000.000)*1000;
select ceiling(ceiling(100000.0003)/1000.000)*1000;```

The result is: 101,000.

More from Kontext