Archives of the TeradataForum

Message Posted: Thu, 30 Aug 2001 @ 09:12:31 GMT


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

Subj:		Re: Using Time(0)

From:		Dieter N�th

Hi Michael,

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',
 time_value time(0)
)
on commit preserve rows;

insert into a values(current_date, current_time);

select
  time_value
  ,date_value
  ,cast(date_value as timestamp(0)) +
   ((time_value - time '00:00:00') hour to second) as "TimeStamp"
from a
;

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 [70] 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 [45] there's no effect.

Dieter


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

Attachments

Library

Quick Reference