Archives of the TeradataForum

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

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

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)

duration

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	Next>	Last>>