Teradata Date Time Part 11 - Time subtraction

Time subtraction gives time interval result.

If a result is to be represented in hours, any minutes or seconds in the remainder will be omitted, as no attempt at rounding takes place.

If no time interval type is specified for the result, an error will be reported. 

select TIME '12:12:12' - TIME '03:03:03';

Above queries fail with 'Invalid Operation on DAteTime or Interval'. The reason being we have not specified an output interval data type.

The result needs to be casted to some interval type. Following examples show implicit casting .

select (TIME '12:12:12' - TIME '03:03:03') HOUR;

(12:12:12 - 03:03:03) HOUR

9

SELECT (TIME '23:59:59.999999' - CURRENT_TIME) MINUTE;

The above query fails because of the output is 1027 minutes and by default the datatype is MINUTE(2) and is incapable of accommodating the result.

Hence we convert the query as follows:

SELECT (TIME '23:59:59.999999' - CURRENT_TIME) MINUTE(4);

(23:59:59.999999 - Current Time(0)) MINUTE

1027