Archives of the TeradataForum

Message Posted: Tue, 29 Jul 2003 @ 09:20:04 GMT

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

Jake Johnson wrote:

Tried that some time ago on different versions, resulting in lots of errors including 3610. So don't run following code if you already have trouble with your dba ;-)

I'd be glad if someone runs the different versions on different Teradata releases and reports the results...

Dieter

ct xxx(ts timestamp(0));

ins xxx(current_timestamp(0))
;ins xxx(current_timestamp(0) + interval '02 02:02:02' day to second)
;ins xxx(current_timestamp(0) + interval '22 22:22:22' day to second)
;ins xxx(current_timestamp(0) + interval '222 02:02:02' day to second)
;ins xxx(current_timestamp(0) + interval '2220 03:33:33' day to second)
;

/*** The following should work in V2R3+
      It reminds me of interval calculations in V2R2 ;-)

      Possible interval overflow (9999 days = 27 years) in calculation
         (ts - timestamp '2000-01-01 00:00:00') day(4)
***/
sel
   ts,
   cast(#seconds/86400 as interval day(4)) +
   cast((#seconds mod 86400) / 3600 as interval hour) +
   cast(#seconds mod 3600 as interval second(4,0))
from
  (
   sel
     ts
     ,mdiff
       (cast(((ts - timestamp '2000-01-01 00:00:00') day(4)) as integer) * 86400
            + extract(hour from ts) * 3600
            + extract(minute from ts) * 60 + extract(second from ts)
        , 1, ts asc) as #Seconds
   from xxx
  ) dt
order by 1
;

/*** I can't remember, when MDIFF started to work on Intervals (V2R4?)
***/
sel
   ts
   ,mdiff((ts - timestamp '2000-01-01 00:00:00') day(4) to
second(0),1,ts)
from xxx
;

/*** CAUTION:
      in V2R5 MDIFF maybe easily replaced by ANSI SQL,
      but this results in a 3610 on V2R.05.00.00.17
***/
sel
   ts
   ,(ts - max(ts ) over (order by ts rows between 1 preceding and 1
preceding)) day(4) to second(0)
from xxx
;

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