All Forums UDA
papvan 3 posts Joined 10/09
29 Oct 2009
Interval Overflow Error

Hi,I am having a column where the time is sorted as ddd hh:min:ss. The col has many 3 digit days & when database sums them all...the total is more than "4 digit days".If I try for interval: day(5) to second(0)....its not working.Is there a way to accomodate sum of days more than 4 digits? 953 21:34:15 846 19:54:20 748 03:24:65 702 11:34:94 675 23:42:58 644 20:13:40 562 23:08:55 534 16:12:39 468 12:53:58 394 15:25:14 343 08:12:28 298 07:30:98 250 05:39:74Let me know, how it can be resolved. Thanks

Jimm 298 posts Joined 09/07
30 Oct 2009

The code below converts each interval to days (with decimal part-days), adds them up and then converts it back to a character interval looking string with up to 5 days.You can only have a maximum of Days(4) to Second interval. (This can easily be upded to larger total of days if things start to take even longer in your busiiness!)-------------------------------------------Set up Test Data:Create Volatile Table T1 (Pk Smallint Not Null, IntvlCol Interval Day(3) TO SECOND(0) )Unique Primary Index (PK)On Commit preserve Rows;Insert Into T1 Values (1,'953 21:34:15');Insert Into T1 Values (2,'846 19:54:20');Insert Into T1 Values (3,'748 03:24:65');Insert Into T1 Values (4,'702 11:34:94');Insert Into T1 Values (5,'675 23:42:58');Insert Into T1 Values (6,'644 20:13:40');Insert Into T1 Values (7,'562 23:08:55');Insert Into T1 Values (8,'534 16:12:39');Insert Into T1 Values (9,'468 12:53:58');Insert Into T1 Values (10,'394 15:25:14');Insert Into T1 Values (11,'343 08:12:28');Insert Into T1 Values (12,'298 07:30:98');Insert Into T1 Values (13,'250 05:39:74');----------------------------------------------Add them up:SelectSum( Extract(Day From IntvlCol) + ((Extract(Hour From IntvlCol) * 60 * 60) + (Extract(Minute From IntvlCol) *60) + Extract(Second From IntvlCol)) / (24.0000 * 60 * 60) ) (Named AllInts), Trim(Cast((Allints (Integer)) As Char(6)))||' '||Cast((Allints Mod 1.0000 *24 (ByteInt)) AS Char(2))||':'||Cast(((AllInts Mod 1.0000 * 24 * 60 Mod 60) (ByteInt)) As Char(2))||':'||Cast(((AllInts Mod 1.0000 * 24 * 60 * 60 ) Mod 60 (ByteInt)) As Char(2)) As BigIntvl From T1Order By 1;HTH

You must sign in to leave a comment.