create function GetIntervalString
(
 in dt1 timestamp,
 in dt2 timestamp,
 /* Interval types:
    1: YEAR
    2: YEAR TO MONTH
    3: MONTH
    4: DAY
    5: DAY TO HOUR
    6: DAY TO MINUTE
    7: DAY TO SECOND
    8: DAY TO MSECOND
    9: HOUR
   10: HOUR TO MINUTE
   11: HOUR TO SECOND
   12: HOUR TO MSECOND
   13: MINUTE
   14: MINUTE TO SECOND
   15: MINUTE TO MSECOND
   16: SECOND
   17: SECOND TO MSECOND
   18: MSECOND
 */
 in interval_type smallint
)
returns varchar(21)
begin
 return
 case interval_type
   when  1 then cast((dt1-dt2) year as varchar)
   when  2 then cast((dt1-dt2) year to month as varchar)
   when  3 then cast((dt1-dt2) month as varchar)
   when  4 then cast((dt1-dt2) day as varchar)
   when  5 then cast((dt1-dt2) day to hour as varchar)
   when  6 then cast((dt1-dt2) day to minute as varchar)
   when  7 then cast((dt1-dt2) day to second as varchar)
   when  8 then cast((dt1-dt2) day to msecond as varchar)
   when  9 then cast((dt1-dt2) hour as varchar)
   when 10 then cast((dt1-dt2) hour to minute as varchar)
   when 11 then cast((dt1-dt2) hour to second as varchar)
   when 12 then cast((dt1-dt2) hour to msecond as varchar)
   when 13 then cast((dt1-dt2) minute as varchar)
   when 14 then cast((dt1-dt2) minute to second as varchar)
   when 15 then cast((dt1-dt2) minute to msecond as varchar)
   when 16 then cast((dt1-dt2) second as varchar)
   when 17 then cast((dt1-dt2) second to msecond as varchar)
   when 18 then cast((dt1-dt2) msecond as varchar)
   else 'invalid interval type'
 end;
end

create table intervals as
select
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',1),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',2),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',3),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',4),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',5),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',6),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',7),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',8),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',9),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',10),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',11),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',12),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',13),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',14),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',15),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',16),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',17),
 GetIntervalString(current_timestamp,timestamp'2000-01-01 00:00:00',18)
from dummy
with data

Error:
Access violation at address 0058D9C1 in module 'edbmgr.exe'. Read of address
0000001C

It works fine if WITH NO DATA is specified instead of WITH DATA.

Ole Willy Tuv

Ole,

<< Error:
Access violation at address 0058D9C1 in module 'edbmgr.exe'. Read of
address 0000001C

It works fine if WITH NO DATA is specified instead of WITH DATA. >>

EDB is in a state of modification right now (views), so I will test this
later and see what I can find.

--
Tim Young
Elevate Software
www.elevatesoft.com