TechNet
Export (0) Print
Expand All

- (Subtract) (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Subtracts two numbers (an arithmetic subtraction operator). Can also subtract a number, in days, from a date.

Topic link icon Transact-SQL Syntax Conventions

  
-- Core Syntax (common to SQL Server and SQL Database)  
expression - expression  

-- Azure SQL Data Warehouse and Parallel Data Warehouse  
      expression   
      -  
      expression  
  

expression
Is any valid expression of any one of the data types of the numeric data type category, except the bit data type. Cannot be used with date, time, datetime2, or datetimeoffset data types.

Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

A. Using subtraction in a SELECT statement

The following example calculates the difference in tax rate between the state or province with the highest tax rate and the state or province with the lowest tax rate.

Applies to: SQL Server and SQL Database.

-- Uses AdventureWorks  
  
SELECT MAX(TaxRate) - MIN(TaxRate) AS 'Tax Rate Difference'  
FROM Sales.SalesTaxRate  
WHERE StateProvinceID IS NOT NULL;  
GO  

You can change the order of execution by using parentheses. Calculations inside parentheses are evaluated first. If parentheses are nested, the most deeply nested calculation has precedence.

B. Using date subtraction

The following example subtracts a number of days from a datetime date.

Applies to: SQL Server and SQL Database.

-- Uses AdventureWorks  
  
DECLARE @altstartdate datetime;  
SET @altstartdate = CONVERT(DATETIME, ''January 10, 1900 3:00 AM', 101);  
SELECT @altstartdate - 1.5 AS 'Subtract Date';  

Here is the result set:

Subtract Date

-----------------------

1900-01-08 15:00:00.000

(1 row(s) affected)

C: Using subtraction in a SELECT statement

The following example calculates the difference in a base rate between the employee with the highest base rate and the employee with the lowest tax rate, from the dimEmployee table.

-- Uses AdventureWorks  
  
SELECT MAX(BaseRate) - MIN(BaseRate) AS BaseRateDifference  
FROM DimEmployee;  

Arithmetic Operators (Transact-SQL)
- (Negative) (Transact-SQL)
Data Types (Transact-SQL)
Expressions (Transact-SQL)
Built-in Functions (Transact-SQL)
SELECT (Transact-SQL)
-= (Subtract EQUALS) (Transact-SQL)
Compound Operators (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft