Teradata DATE-TIME interval part 6 - Casting Teradata Interval types

• CAST with Interval data types:

As mentioned earlier we can cast any of the YEAR-MONTH interval type to any other YEAR-MONTH interval type

We can also make mathematical operations such as division and multiplication on the interval types.

Example:

SELECT CAST( (INTERVAL '3-06' YEAR TO MONTH) AS INTERVAL MONTH) ;

 3-06

 42

SELECT CAST ((INTERVAL '3-02' YEAR TO MONTH) AS INTERVAL YEAR);

3

SELECT INTERVAL '3-02' YEAR TO MONTH / 6;

0-06

SELECT INTERVAL '8' YEAR / 3;

2

Note the following query .

Select  CAST ( ( DATE '2013-07-07' - DATE '2013-04-04') AS INTERVAL MONTH);

(2013-07-07-2013-04-04)

1         94

Note the result is shown as 94, which seems wrong because we wanted to have date difference result as months . However the result is the actual number of days. The reason is way the query worked. We first subtracted the dates and the result is 94 . As we know result of date subtraction is integer , the integer 94 gets casted to data type month, which changes just the data type and not the value. Thus we get the result which is nothing by integer casted to date value.

The above query is equivalent to the one given below

SELECT CAST ( (95-1) AS INTERVAL MONTH);

94

We can get the result we want by using the implicit cast as show below:

Select  ( DATE '2013-07-07' - DATE '2013-04-04')  MONTH;

3

I don’t know whats the difference between impicit and explicit cast.

Select  CAST ( ( DATE '2013-07-07' - DATE '2013-04-04') AS INTERVAL YEAR TO MONTH);

This query will fail because result of minus will be a integer which is not of the format yy-mm and hence will cause the query to fail because of invalid operation on Datetime or interval.