IDS 7.4 SPL - casting interval to integer

sourceopen · #1 (**permalink**) 08-03-08, 18:53

Hi All,

I'm having troubles trying to cast an interval hour(3) to second to second(9) to second, or integer.

The purpose is to bring durations down to seconds to calculate the percentage of one against the other.

Has anyone had dealings with this - i keep getting incompatible type casting errors.

Cheers!

ceinma · #2 (**permalink**) 08-04-08, 12:53

post a example of the code that you are using.

ceinma · #3 (**permalink**) 08-04-08, 13:28

ok... i looking for information on the manuals...
on the manual (SQL Reference) of IDS 11.50 say this: "The database server supports no implicit casts between these two categories of INTERVAL data types.". This means you __need__ to use the CAST or "::" operator to convert a INTERVAL data type.

executed a few tests... this works well on IDS 11.50 but not work on 7.31 , I not sure, but the CAST (if exists) on the 7.31 are limited to DATETIME data types...

Code:

 create temp table tp01 ( int1 interval day to second, int2 interval hour to second);

 insert into tp01 values ('45 23:20:10', '44:10:44');

 select * from tp01;
 update tp01 set int2 = int2 + interval(00:51:00) hour to second where 1=1;
 select * from tp01;
 select cast(int2 as interval second(9) to second) from tp0

This is the output of the SQL above , running on IDS 11.50

Code:

 45 23:20:10  45:01:44
int1         int2

 45 23:20:10  44:10:44


int1         int2

 45 23:20:10  45:01:44


(expression)

    162104

Internet Services

Web Development

Digital Marketing

Consumer Tech