Archives of the TeradataForum

Message Posted: Thu, 10 Feb 2005 @ 06:12:21 GMT

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

On Wed, 9 Feb 2005 13:07:53 -0800, Ganga Palakattil wrote:

the simplest ( short of writing a UDF ) to do this is....

Divide and mod and build your own timestamp(6).

Sorry about the

(cast ((millisecs / 1000 ) as integer))

but Millisecs as a number is too big for an integer so Teradata Automatically makes it a float which causes rounding errors on some of the calculations.

The / 1000 makes the value seconds which is easier to operate on throughout the calculation.

To be consistent I used that notation for seconds every where.

     sel  1107982900000 millisecs,
      cast (
         (( date '1970-01-01' +
         ( (cast ((millisecs / 1000 )  as integer))
                 /  86400 ) ) ( format 'yyyy-mm-dd'))
                || ' ' ||
           ((((cast ((millisecs / 1000 )  as integer))
                  mod 86400) / 3600 ) (format '99' ))
                   || ':' ||
           ((((cast ((millisecs / 1000 )  as integer))
                  mod 3600 )  / 60) (format '99' ))
                   || ':' ||
           ((((cast ((millisecs / 1000 )  as integer))
                      mod (60))) (format '99' ))
                   || '.' ||
           ((millisecs mod 1000) (format '999' ))
                   || '000'
                as timestamp(6) )  mydate
     ;

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