Teradata DATE-TIME Part 4 - YEAR MONTH DAY interval

• Year Month interval

Interval size range is n = 1 to 4

Default size range = 2,INTERVAL YEAR would mean -99 to 99 years

INTERVAL YEAR(n):	-9999 to +9999 years
INTERVAL MONTH(n):	-9999 to +9999 months
INTERVAL YEAR(n) TO MONTH:	-9999 to 9999 years and 0-12 months

Intervals may be represented as literals in SQL scripts. Characters strings should ensure that they conform to the properties of interval types.

Default size is 2. Size is not specified with literals.

Examples of Literals:

INTERVAL -'30' YEAR --> - 30 years. Note that negative sign should be outside the single quotes.

INTERVAL '3' MONTH --> 3 months . + sign is default.

INTERVAL '5-08' YEAR TO MONTH --> 5 years and 8 months. Note that when representing  year to month the literal must be of the format 'yy-mm'

Interval Data Type	Internal storage	Range
YEAR	SMALLINT	-9999 to +9999 years
YEAR TO MONTH	SMALLINT	-9999 to 9999 years 0 to 12  months
MONTH	SMALLINT	0 to 12 Months

• DAY interval

INTERVAL DAY(n) -9999 to 9999 days (n = 1 to 4)

Example of DAY literal:

INTERVAL -'325' DAY                   - 325 days

Interval Data Type	Internal storage	Range
DAY	SMALLINT	-9999 to +9999 years

• We can combine Date data type with literal interval types to get a different date.

We can also combine interval literals with intervals to get a different intervals.

Dates may be subtracted from other dates to get a interval types.

As mentioned earlier dates may be combined with the following specific interval types

DATE - DATE =  INTERVAL

DATE + INTERVAL = DATE

INTERVAL + INTERVAL = INTERVAL

INTERVAL */ n = INTERVAL ( Division and multiplication of the interval returns

Select

date,

interval '2' year + interval '4' month, /* combining intervals with intervals to get an interval*/

date + interval '2' year , /* combining Date with interval to get a date */

date - interval '3' year ,

date + interval '2' day ,

(INTERVAL '4-10' YEAR TO MONTH) -  (INTERVAL '2' YEAR),

INTERVAL '6' YEAR / 5 /* Multiplication and division can be performed on interval data types*/

Date	( 2+ 4)	(Date+ 2)	(Date- 3)	(Date+ 2)	( 4-10- 2)	( 6/5)
3/31/2013	2-04	3/31/2015	3/31/2010	4/2/2013	2-10	1

• DATE literals must be represented using the ANSI standard date format, regardless of the DATEFORM settings

Earlier we showed that DATE data type columns could be combined with interval data types. Similarly date literals can be combined with interval data types. Thus both date literals and date data types could be combined with intervals.

ANSI standard Date format is 'YYYY-MM-DD'

Date literals could be added to any of the following literals: YEAR,MONTH, YEAR TO MONTH, DAY

Select DATE '2014-12-21' + INTERVAL '4' YEAR;

• Caution : We need to keep caution while using INTERVAL MONTH with dates.

Say below query

SELECT DATE '2013-02-02' + INTERVAL '2' MONTH;

The above query will just add 2 to the months and return the result. In this particular case its fine, but we may also end up with wrong dates.

Example:

SELECT DATE ' 2012-12-31' + INTERVAL '2' MONTH.

The above query will also increment the month part by 2 and end up with a date of 31st Feb which is a invalid date and hence the query will fail with error code 2665 which is invalid date.

Hence for doing intelligent date arithmetic it is suggested we use the function ADD_MONTHS

select ADD_MONTHS (DATE '2012-12-31',2);