Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric Preview
Subtracts two numbers (an arithmetic subtraction operator). Can also subtract a number, in days, from a date.
Transact-SQL syntax conventions
Syntax
expression - expression
Arguments
expression
Any valid expression of any one of the data types of the numeric data type category, except the bit data type. Can't be used with date, time, datetime2, or datetimeoffset data types.
Return types
Returns the data type of the argument with the higher precedence. For more information, see Data type precedence.
Examples
The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A. Use 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.
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. Use date subtraction
The following example subtracts several days from a datetime date.
Applies to: SQL Server and SQL Database.
DECLARE @altstartdate DATETIME;
SET @altstartdate = CONVERT(DATETIME, 'January 10, 1900 3:00 AM', 101);
SELECT @altstartdate - 1.5 AS 'Subtract Date';
Here's the result set.
Subtract Date
-----------------------
1900-01-08 15:00:00.000
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C. Use 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.
SELECT MAX(BaseRate) - MIN(BaseRate) AS BaseRateDifference
FROM DimEmployee;