Teradata DATE-TIME interval Part 5 - Date subtraction

Date subtraction gives us number of days between the two dates. Note that result is integer type.

DATE minus DATE could give us either an interval(YEAR,MONTH,DAY,YEAR TO MONTH) or an integer.

By default the result is always an integer. However we can apply conversion to get results in any particular interval type.

select DATE - DATE '2013-01-01';

(Date-2013-01-01)

90

We can also get negative values as the result:

Select  ( DATE '2013-01-02' - DATE '2013-04-04');

(2013-01-02-2013-04-04)

-92

To know the return type of the result we could use the type function as follows:

Select TYPE ( DATE - DATE '2013-01-01')

Type((Date-2013-01-01))

INTEGER

To get the result in interval type we do an implicit cast as shown below.

select ( DATE - DATE '2013-01-01') MONTH   ---> Technique of performing implicit cast

(Date - 2013-01-01) MONTH

3

Note that when doing implicit conversion we don’t use the keyword 'INTERVAL'.

Note that when converting to interval types we don’t use the keyword INTERVAL.

select TYPE(( DATE - DATE '2013-01-01') MONTH);

Type((Date - 2013-01-01) MONTH)

INTERVAL MONTH(2)

Notice the below result.

SELECT DATE - DATE '2009-01-01';

(Date-2009-01-01)

1552

However if we write the same query as below we would get an error.

SELECT (DATE - DATE '2009-01-01') DAY;

Error Msg: SELECT Failed. 7453:  Interval field overflow. 

The reason is that by default the length is 2 for the interval DAY data type. .ie DAY(2).

To be able to see the result we change the data type to DAY(4) as shown below:

SELECT (DATE - DATE '2009-01-01') DAY(4);

(Date - 2009-01-01) DAY

1552

If the result of the date subtraction is more than 9999 days we wont be able to convert the result to interval DAY data type.

Example :

SELECT (DATE - DATE '1909-01-01') DAY(4); --> will fail due to interval field overflow. The result is 5 digits and cannot be represented by DAY(4).

SELECT DATE - DATE '1909-01-01';

(Date-1909-01-01)

38077

We can get the same results in number of months.

SELECT (DATE - DATE '1909-01-01') MONTH(4);

(Date - 1909-01-01) MONTH

1251

In all the above examples we have been using Teradata implicit cast. However we can also use explicit cast to perform the conversion.