Message Posted: Fri, 30 Apr 2004 @ 19:55:41 GMT

 < Last>>

 Subj: Re: Converting time in miliseconds to TIEMSTAMP in teradata From: Maxwell, Donald

Interesting problem!

Try

```     SELECT
86399999 (DECIMAL(18,0)) T
,CAST ( ('1969-12-31' (DATE)) + T / 86400000.00000000 AS TIMESTAMP(3) ) + INTERVAL '17' HOUR
+ (T / 1000.000 MOD 86400) * CAST('00:00:01' AS INTERVAL HOUR TO SECOND)
```

There are several things happening in this computation:

1) Add the number of whole days (T / 86400000.00000000) to your starting date.

1) Teradata has a max DECIMAL(18,0) which is less than your parameter DECIMAL(19,0)

2) The number 86400000 is the number of milliseconds in a day. In order to avoid rounding, you must use the DECIMAL(16,8) representation.

3) The remaining seconds are found by (T / 1000.000 MOD 86400), which is multiplied by the one-second HOUR TO SECOND INTERVAL to get the correct remaining HOUR TO SECOND duration.

For what it's worth, I came across an interesting bug while writing this solution. Multiplying intervals by a decimal number works correctly only for the hours, minutes, and seconds components. The fractional-second component does not multiply higher than the seconds component. To see what I mean, try the following SELECT:

```     SELECT
CAST('00:00:00.1' AS INTERVAL HOUR TO SECOND) A
,999 * A
,1000 * A
Donald Maxwell

<Prev
Next>

<<First
<Prev
Next>
Last>>

```

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback