SQL 2005+

SQL Server Arithmetic Operators

by Richard Carr, published at http://www.blackwasp.co.uk/SQLArithmeticOperators.aspx

The thirty-second part of the SQL Server Programming Fundamentals tutorial describes the basic arithmetic operators provided by Transact-SQL (T-SQL). These operators allow simple mathematical operations within queries, procedures and other statements.

Unary Operators

The addition and subtraction operators can be used as unary operators, meaning that only one operand is required. By prefixing a value with the subtraction operator the value is negated. Using a prefix of "+" has no effect but can enhance the clarity of code in some situations:

PRINT -5    -- -5
PRINT +5    -- 5

Using Arithmetic Operators with DateTimes

The addition and subtraction operators can be used with DateTime and SmallDateTime values to adjust the date and time. This is useful in simple situations, particularly when adding or subtracting a whole number of days, as a value of 1 represents a single day. If you wish to add or subtract a smaller unit of time, such as hours, minutes or seconds, it is easier to use date and time functions. We will review those functions later in the tutorial.

DECLARE @SomeTime DATETIME
SET @SomeTime = '2009-08-15T21:00:00'
PRINT @SomeTime + 1

-- Outputs 'Aug 16 2009  9:00PM'

Operator Precedence

When more than one operator is used within a single statement, the operators are evaluated in a particular order. This order is controlled by the rules of precedence. Over the course of the next few articles we will build a table describing the order of precedence of operators in T-SQL. So far, the precedence is as follows:

In this table, the operators that are at the top are evaluated before those that at the bottom. Within each section, the more important operators are to the left. These are interpreted before those to their right.

Using Parentheses to Modify the Operator Execution Order

If you create an expression that contains several operators, you may wish to modify the order in which they are processed. This can be achieved using pairs of parentheses (). When calculating the results of an expression, elements within parentheses will always be processed before those outside. Multiple pairs of parentheses may be used and nested as required.

PRINT 12 + 6 / 2 + 1        -- 16
PRINT (12 + 6) / (2 + 1)    -- 6
PRINT ((12 + 6) / 2) + 1    -- 10