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.5 HotFix 1
- 6.5.1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Intervals and Date/Time Values

Intervals can be added to or subtracted from date/time datatypes. Such arithmetic expressions will always evaluate to the same datatype as the date/time value in the expression. The base arithmetic expressions and their result types are summarized in the following tables:

DATE: The following table provides the DATE expressions:

Expression
Result Type

<DATE> – <date interval>
DATE

<DATE> + <date interval>
DATE

<date interval> + <DATE>
DATE
TIME: The following table provides the TIME expressions:

Expression
Result Type

<TIME> – <time interval>
TIME

<TIME> + <time interval>
TIME

<time interval> + <TIME>
TIME
TIMESTAMP: The following table provides the TIMESTAMP expressions:

Expression
Result Type

<TIMESTAMP> – <timestamp interval>
TIMESTAMP

<TIMESTAMP> + <timestamp interval>
TIMESTAMP

<timestamp interval> + <TIMESTAMP>
TIMESTAMP

<TIMESTAMP> – <date interval>
TIMESTAMP

<TIMESTAMP> + <date interval>
TIMESTAMP

<date interval> + <TIMESTAMP>
TIMESTAMP

<TIMESTAMP> – <time interval>
TIMESTAMP

<TIMESTAMP> + <time interval>
TIMESTAMP

<time interval> + <TIMESTAMP>
TIMESTAMP
Examples: The following table provides examples for expressions:

Expression
Result

13 DAYS + '2003-12-19'
2004-01-01

'11:30:45' - 156 SECONDS
11:28:09

'2003-12-19-12.35.52.544000' - 75 HOURS
2003-12-16-09.35.52.544000

Expression	Result Type
<DATE> – <date interval>	DATE
<DATE> + <date interval>	DATE
<date interval> + <DATE>	DATE

Expression	Result Type
<TIME> – <time interval>	TIME
<TIME> + <time interval>	TIME
<time interval> + <TIME>	TIME

Expression	Result Type
<TIMESTAMP> – <timestamp interval>	TIMESTAMP
<TIMESTAMP> + <timestamp interval>	TIMESTAMP
<timestamp interval> + <TIMESTAMP>	TIMESTAMP
<TIMESTAMP> – <date interval>	TIMESTAMP
<TIMESTAMP> + <date interval>	TIMESTAMP
<date interval> + <TIMESTAMP>	TIMESTAMP
<TIMESTAMP> – <time interval>	TIMESTAMP
<TIMESTAMP> + <time interval>	TIMESTAMP
<time interval> + <TIMESTAMP>	TIMESTAMP

Expression	Result
13 DAYS + '2003-12-19'	2004-01-01
'11:30:45' - 156 SECONDS	11:28:09
'2003-12-19-12.35.52.544000' - 75 HOURS	2003-12-16-09.35.52.544000

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Intervals and Date/Time Values

Intervals and Date/Time Values