Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 29 Mar 2003 @ 05:15:53 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Timestamp difference
From:   Terry Stover

You shouldn't have to use a case statement, here's a code fragment that creates a timestamp from 2 pairs date and time columns and outputs hhh:mm:ss. In the source table the dates are type date and the times are float.

cast(cast(cast(logdate as  format 'YYYY-MM-DD') as char(11)) || cast
(logtime as  format '99:99:99') as timestamp(0)) -
cast(cast(cast(logondate as  format 'YYYY-MM-DD') as char(11)) || cast
(logontime as  format '99:99:99') as timestamp(0))  hour(3) to second(0)


Casting the date as char(11) leaves the space between the date and time so you can convert to timestamp. In a similar problem I encountered some cases where the time rounded up from 59:59:59.xxx to 60:00:00, generating an invalid format error. Timestamp(1) or rounding the time before conversion could solve that problem.

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