We help IT Professionals succeed at work.

Convert 'interval hours to seconds'

van-coug asked
Does anyone know of a quick way to convert data type of interval days to seconds (e.g. +534 11:45:36.000000) to display as just days (e.g. 534.5). Ideally, I would like to throw out weekends. I’m using crystal reports XI and oracle 10 DB. I don’t care if it’s a crystal or oracle function. I think I can do it using extract, but I was hoping there was maybe a function to do it. Is extracting the day, then extracting the hour the only way?
Watch Question

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Is your data in the field as shown?

You could write a formula like
Local StringVar Array Interval;
Local NumberVar myHours;
Local numberVar myMinutes;
Local NumberVar mySeconds;
Local NumberVar myDays;

Interval := Split({YourIntervalField},' ');
myDays := Interval[1];
myHours := Val(Split(Interval[2],':')[1]);
myMinutes:= Val(Split(Interval[2],':')[2]);
mySeconds:= Val(Split(Interval[2],':')[3]);

mySeconds := mySeconds + 60 * myMinutes + 60*60*myHours;

myDays := myDays + mySeconds / (60*60* 24)



Thanks- mlmcc, much appreciated