We help IT Professionals succeed at work.

Convert 'interval hours to seconds'

van-coug
van-coug asked
on
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?
Comment
Watch Question

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
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)

mlmcc

Author

Commented:
Thanks- mlmcc, much appreciated