Archives of the TeradataForum

Message Posted: Fri, 05 Apr 2002 @ 18:04:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

In trying to understand why/how the SQL from Kent works, ...

(widen window to make following text readable)
When we do 'datetime - datetime' the result is a type INTERVAL. Now it gets confusing - is INTERVAL by itself a datatype? or does it always need to be qualified with the fields - thus 'INTERVAL year to month' etc is a datatype, while just 'interval' is not a datatype.

Date math:
When we do 'date -date' this results always and only in an 'INTERVAL DAY' (right?), thus :

Select date '2001-01-01' - date '2001-01-02'              ; works fine.
Select date '2001-01-01' - date '2001-01-02' year to day  ; error 3706 !!

(why?)

Time math:
When we do 'time - time' this results in an 'interval - but unknown fields???' I would have expected this to result in 'interval hour to second' as default - but I guess it does not, and I have to explicitly call out the fields:

select time '00:00:15'-time '00:00:15'                 ; error 5407
select time '00:00:15'-time '00:00:15' hour to second  ; works fine
select time '00:00:15'-time '00:00:15'  second         ; works fine

Timestamp math:
When we do 'timestamp - timestamp', this results in an interval with unknow fields?? An 'interval year to second' does not exist anyway. Thus, again we need to explicitly qualify the fields:

select timestamp '2002-01-01 00:00:15' - timestamp '2002-01-01 00:01:17' day to second; works fine
select timestamp '2002-01-01 00:00:15' - timestamp '2002-01-01 00:01:17' second(3); works fine

Related question:

In the SQL

select time '00:00:15'-time '00:00:15' hour to second;

what does 'hour to second' do? Is it doing a conversion/casting?

Kumaran Anantaraman

	<Prev	Next>		<<First	<Prev	Next>	Last>>