Archives of the TeradataForum
Message Posted: Fri, 29 Mar 2003 @ 05:15:53 GMT
| 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.