Reference Guide > TDV Support for SQL Operators > Arithmetic Operators > Subtract
 
Subtract
The subtract operator ( - ) subtracts the second operand from the first operand and returns the difference.
Note: A configuration parameter is available to control whether this operator allows precision/scale to exceed 38. See Decimal Digit Limitation on Functions, for details.
DECIMAL and NUMERIC Data Types
When the subtract operator is applied to operands that include DECIMAL or NUMERIC data types, the output data type, precision and scale might depend on the data type, precision and scale of the operands, as shown below.
Syntax
operand1 - operand2
Remarks
• The order of the inputs (operands) has no effect on the output data type.
• The outputs for DECIMAL and NUMERIC data types combined with other operands are shown in the table.
Inputs
Output
DECIMAL(p1,s1) - DECIMAL(p2,s2)
DECIMAL(p3,s3), with p3 the larger precision of the inputs, and s3 the larger scale of the inputs.
DECIMAL(p1,s1) - NUMERIC(p2,s2)
NUMERIC - NUMERIC
NUMERIC
DECIMAL(p,s) - not-DECIMAL-or-NUMERIC
DECIMAL(p,s)
NUMERIC - not-DECIMAL-or-NUMERIC
NUMERIC
Examples
DECIMAL(6,1) - DECIMAL(5,2) -> DECIMAL(6,2)
DECIMAL(6,1) - NUMERIC(5,2) -> DECIMAL(6,2)
NUMERIC(6,1) - NUMERIC(5,2) -> NUMERIC(6,2)
INTERVAL Type
INTERVAL can be subtracted from DATE, TIME, TIMESTAMP or another INTERVAL.
Syntax
operand1 - operand2
Remarks
• INTERVAL can be subtracted from DATE, TIME, TIMESTAMP, or another INTERVAL.
• Interval days, hours, minutes, or seconds can only be subtracted from other interval days, hours, minutes, or seconds. Interval years or months can only be subtracted from other interval years or months. The two groups of units are not interchangeable.
• When subtracting months, the TDV Server does not round down the day of the month, and it might throw an error if the day of the month is invalid for the specified month.
• The order of the inputs (operands) has no effect on the output data type.
• The outputs for INTERVAL as a subtract operand are shown in the table.
Inputs
Output
DATE - INTERVAL
DATE. Only days, months, and years can be subtracted from a DATE.
INTERVAL - INTERVAL
INTERVAL
INTERVAL - DATE
DATE. Dates can be subtracted from INTERVALs only if the INTERVAL is days, months, or years.
INTERVAL - TIME
TIME
INTERVAL - TIMESTAMP
TIMESTAMP
Examples
TIME '7:00:00' - INTERVAL '0 3:00:00' DAY TO SECOND = TIME '4:00:00'
INTERVAL '10000-11' YEAR(5) TO MONTH - INTERVAL '1' MONTH(1) = INTERVAL '10000-10'
  YEAR TO MONTH
DATE '1999-12-31' - INTERVAL '365' DAY(3) = DATE '1998-01-01'
Mixed Data Types
The subtract operator can be applied to operands that have a wide variety of data types, including operands comparable or castable to data types that can accept arithmetic operators.
Syntax
operand1 - operand2
Remarks
The operand data types and resulting output data types are shown in the table.
Operand1
Operand2
Output
TINYINT
TINYINT
INTEGER
SMALLINT
SMALLINT
INTEGER
INTEGER
BIGINT
BIGINT
TINYINT
SMALLINT
INTEGER
BIGINT
STRING
INTEGER
TINYINT
FLOAT
REAL
FLOAT
SMALLINT
INTEGER
BIGINT
TINYINT
SMALLINT
INTEGER
BIGINT
DECIMAL (p,s)
NUMERIC (p,s)
DECIMAL (p,s)
FLOAT
REAL
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
FLOAT
REAL
FLOAT
REAL
FLOAT
DECIMAL (p,s)
REAL
DECIMAL (p,s)
NUMERIC (p,s)
DECIMAL
DECIMAL
NUMERIC
TINYINT
SMALLINT
INTEGER
BIGINT
STRING
DECIMAL
NUMERIC
FLOAT
REAL
DECIMAL
DECIMAL
NUMERIC
DECIMAL
NUMERIC
DECIMAL
DATE
DATE
An INTERVAL day: the number of days between the two arguments.
DATE '2006-03-20' - DATE '2005-12-02' = INTERVAL '108' DAY(3)
DATE
TIMESTAMP
STRING
An INTEGER that represents the difference between the dates in the two inputs.
TIME
TIME
An INTERVAL hour to second.
TIME '21:00:00' - TIME '19:00:00' = INTERVAL '0 2:00:00' DAY TO SECOND
TIMESTAMP
TIMESTAMP
An INTERVAL day to second.
TIMESTAMP '2006-03-20 21:00:00' - TIMESTAMP '2005-12-02 19:00:00' = INTERVAL '108 02:00:00' DAY(3) TO SECOND
TIMESTAMP
DATE
STRING
An INTEGER that represents the difference between the dates in the two inputs.
STRING
STRING
TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP
DECIMAL
INTEGER
INTEGER
INTEGER
INTEGER
DECIMAL
FLOAT
FLOAT
DECIMAL
INTEGER
INTEGER
NULL
TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP
STRING
NULL
NULL
TINYINT
SMALLINT
INTEGER
BIGINT
NUMERIC
FLOAT
REAL
DECIMAL
DATE
TIMESTAMP
STRING
NULL