Home Page for the TeradataForum
 

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016