Archives of the TeradataForum

Message Posted: Thu, 04 Feb 2010 @ 14:16:25 GMT

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

<-- Anonymously Posted: Thursday, February 04, 2010 09:12 -->

We currently do the conversion as:-

     Select
      blkingUSER, blkingLOGHOST, blkingSESSNO
     ,cast( (begdate(FORMAT'YYYY-MM-DD ') (char(11)) )
              || (begtime(FORMAT'99:99:99.999') (char(12)) ) as timestamp(3) ) (named begdatetime)
     ,cast( (begdate(FORMAT'YYYY-MM-DD ') (char(11)) )
              || (begtime(FORMAT'99:99:99.999') (char(12)) ) as timestamp(3) )
      +  cast(max(cast(DELAY   as char(12))) as INTERVAL HOUR TO SECOND(3))   (named enddatetime)
     from SYS_REPORTING.all_locks
     where begdate between date-1 and date
     and delay > 0
     group by 1, 2, 3, 4
     order by 1, 2, 3, 4

Is this the most efficient way of coding it, Begdate is defined as Date, Begtime and Delay are defined as FLOAT.

I've tried converting the begdate to timestamp(3) and adding in the begtime, but produced a 5407 error, using SQL Assitant (date format set to III).

Many thanks

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