Archives of the TeradataForum
Message Posted: Thu, 10 Feb 2005 @ 11:01:30 GMT
| Subj: | | Re: Epoch function |
| |
| From: | | Dieter Noeth |
Dennis Calkins wrote:
| | Just out of curiosity is there a way short of making some sort of derived table to assign | |
Instead of "some sort of derived table" use a derived table ;-)
sel millisecs,
mytimestamp
from
(
sel 1107982900000 millisecs,
(cast ((millisecs / 1000 ) as integer)) secs,
cast (
(( date '1970-01-01' + (secs / 86400 ) ) ( format 'yyyy-mm-dd'))
|| ' ' ||
((( secs mod 86400) / 3600 ) (format '99' ))
|| ':' ||
((( secs mod 3600 ) / 60) (format '99' ))
|| ':' ||
((( secs mod (60))) (format '99' ))
|| '.' ||
((millisecs mod 1000) (format '999' ))
|| '000'
as timestamp(6) ) mytimestamp
) dt;
In V2R6 you could use WITH:
with dt (millisecs, secs) as
( sel 1107982900000 millisecs,
(cast ((millisecs / 1000 ) as integer)) secs
)
select
millisecs,
cast (
(( date '1970-01-01' + (secs / 86400 ) ) ( format 'yyyy-mm-dd'))
|| ' ' ||
((( secs mod 86400) / 3600 ) (format '99' ))
|| ':' ||
((( secs mod 3600 ) / 60) (format '99' ))
|| ':' ||
((( secs mod (60))) (format '99' ))
|| '.' ||
((millisecs mod 1000) (format '999' ))
|| '000'
as timestamp(6) ) mytimestamp
from dt;
Dieter
| 
