CEILING and FLOOR: Negative Values Change the Direction

CEILING and FLOOR follow the number line, including its negative side. I don’t describe either operation as removing a fraction, because truncation follows a different rule.

Name the direction

A fractional number sits between two integer boundaries. CEILING selects the smallest integer boundary at least as large as the input. FLOOR selects the largest boundary no larger than the input.

For a positive number, the familiar visual shortcut works. The upper boundary looks like the next whole number. Below zero, the upper boundary is less negative. A rule based only on the visible digits can therefore select the wrong result.

I use a negative counterpart whenever I review this calculation. That case makes the intended direction unavoidable. A positive-only example can conceal a confused specification. Returning an integer-valued result doesn’t identify the operation that produced it.

Compare three operations

The example uses decimal inputs with two fractional places. It includes positive and negative fractions, exact integers, zero and NULL. Each original value remains beside its three results. The third result uses ROUND’s nonzero argument to request truncation.

The two integer-boundary outputs are explicitly converted to decimal(7,0). That is this query’s chosen result contract. It doesn’t assert a universal native return width for either function. The separate truncated output retains two decimal places for comparison.

Every input in the demonstration fits those declared output widths. The query doesn’t intentionally trigger overflow. It also changes no objects or session state. Keep the source value visible when inspecting the complete expected rows.

WITH Inputs AS
(
    SELECT Id, CAST(Amount AS decimal(7,2)) AS Amount
    FROM (VALUES (1, 1.25), (2, -1.25), (3, 2.00),
                 (4, -2.00), (5, 0.00),
                 (6, CAST(NULL AS decimal(7,2))), (7, -0.25)) AS v(Id, Amount)
)
SELECT Id, Amount,
       CAST(CEILING(Amount) AS decimal(7,0)) AS UpperBoundary,
       CAST(FLOOR(Amount) AS decimal(7,0)) AS LowerBoundary,
       CAST(ROUND(Amount, 0, 1) AS decimal(7,2)) AS TruncatedValue
FROM Inputs
ORDER BY Id;

Read the negative case

For 1.25, the upper boundary is 2 and the lower boundary is 1. Truncation produces 1.00. For -1.25, the upper boundary becomes -1. The lower boundary becomes -2.

The truncated negative value is -1.00. It therefore matches CEILING’s integer boundary for this particular negative input. That doesn’t make the functions interchangeable. For the positive counterpart, truncation instead matches FLOOR’s boundary.

An exact integer already sits on a boundary. Both boundary functions keep that integer-valued result. Zero behaves the same way in these examples. NULL remains missing rather than becoming a fabricated boundary value.

Choose a business interpretation

Suppose a calculation asks how many whole containers a positive quantity needs. A boundary operation can express that rounding rule. Negative corrections raise a separate business question. I’d settle the correction policy before reusing the positive formula.

I can defend truncation when a specification explicitly discards fractional units. That is a different requirement from always choosing the lower boundary. The negative case decides which statement is accurate. Function names don’t replace the business definition.

The input type also deserves review outside these small values. Return types and overflow limits depend on the input type. This query deliberately casts its published outputs to known types. A wider or approximate input needs a separately checked type contract.

Keep calculation separate from formatting

A displayed decimal point doesn’t mean a fractional value survives the calculation. Likewise, a grid showing a whole number doesn’t guarantee an int return type. I check the result metadata alongside the values. Presentation and numeric representation answer different questions.

Don’t cast a fractional input to an integer before testing the boundary functions. That earlier conversion can remove the distinction the example is supposed to demonstrate. Give the function the intended source value. Convert the resulting output only for the explicit final contract.

The query includes seven complete rows with explicitly cast output types. Compare both integer outputs beside each original decimal. Check negative values before deciding the rule is correct. A matching positive result is only one part of that decision.

Pick the direction first, then pick the function.

An integer boundary is not a missing fraction, it is the direction your calculation chose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.