Archives of the TeradataForum
Message Posted: Thu, 30 Aug 2001 @ 09:12:31 GMT
| Subj: || || Re: Using Time(0) |
| From: || || Dieter Nöth |
| ||We have some tables that have the time stored in the above datatype that I need to concatenate with a DATE field to make comparisons
between records or to select maximum/minimum combinations etc.|| |
You can't add time to a date. But you can add an interval, so modify the time to be an interval using ((time_value - time '00:00:00')
hour to second)
create volatile table a
date_value date format 'YYYY-MM-DD',
on commit preserve rows;
insert into a values(current_date, current_time);
,cast(date_value as timestamp(0)) +
((time_value - time '00:00:00') hour to second) as "TimeStamp"
| ||However methods that I have tried so far (basic concatenation with/without formatting from manual)have failure written all over
them, the message being 'Invalid on an ANSI datetime...'|| |
| ||I have done this before where the time was held in char or integer format and works fine but I cannot even reformat the datatype to
either of these datatypes prior to concatenation (as same error message applies).|| |
Maybe check your table, if time column _really_ is a TIME. If you create a table with a TIME using Queryman, then on some TD Releases
(testet on V2R.03.00.01.33  and the Demo V2R.04.00.02.01) it depends on the "Allow use of ODBC SQL Extensions in Queries" option: If
it's set then TIME will be an INTEGER!
On V2R.04.00.01.17  there's no effect.