Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

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);

  ,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. [70] and the Demo V2R. 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. [45] there's no effect.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020