About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.4.3 HotFix 1
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3

Back Next

Interval Arithmetic

Intervals can be added to and subtracted from other intervals in most cases.

The following table shows the possible combinations of addition and subtraction between two intervals, and the resulting interval types:

+ \| –	DAYS	MONTHS	YEARS	HOURS	MINUTES	SECONDS	MICRO-SECONDS
DAYS	DAYS	*	*	SECONDS	SECONDS	SECONDS	SECONDS
MONTHS	*	MONTHS	MONTHS	*	*	*	*
YEARS	*	MONTHS	MONTHS	*	*	*	*
HOURS	SECONDS	*	*	SECONDS	SECONDS	SECONDS	SECONDS
MINUTES	SECONDS	*	*	SECONDS	SECONDS	SECONDS	SECONDS
SECONDS	SECONDS	*	*	SECONDS	SECONDS	SECONDS	SECONDS
MICRO-SECONDS	SECONDS	*	*	SECONDS	SECONDS	SECONDS	SECONDS

* You cannot perform an arithmetic operation between the two interval types.

Intervals can also be involved in addition, subtraction, multiplication, and division operations with numeric expressions as operands. The interval can appear on either side of an operator (+
, –
, *
, /
) in all cases except one: a numeric expression cannot be divided by an interval. For example, "

12/(3 days)

" is an invalid arithmetic operation.

The following table lists the interval type resulting from the arithmetic interaction of a numeric expression and an interval of a given type (the result is the same for each operator):

Interval	Result Type
DAYS	DAYS
MONTHS	MONTHS
YEARS	MONTHS
HOURS	SECONDS
MINUTES	SECONDS
SECONDS	SECONDS
MICROSECONDS	SECONDS

Examples of Interval Arithmetic

The following table provides examples for interval arithmetic:

Expression	Result
1 DAY + 1 HOUR	90000.000000 (seconds)
60 SECONDS + 500 MICROSECONDS	60.000500 (seconds)
12 MONTHS + 1 YEAR	24 (months)
1 YEAR * 2	24 (months)
28 DAYS / 7	4 (days)
8 * 2 HOURS	57600 (seconds)
20 MICROSECONDS + 2	2.000020 (seconds)
1 DAY - (30 + 30 MINUTES)	84570.000000 (seconds)

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Interval Arithmetic

Interval Arithmetic

Examples of Interval Arithmetic