## converting HH:MM to a decimal

### converting HH:MM to a decimal

I have 3 pairs of time in/out (24 our clock) that I need to calculate
the decimal equivalent of the total time worked for that day.

example:  time in/out  08:00  10:00, 10:15 12:45, 13:45 16:50
if I use a simple select of  time_out time_ in  I get 2:00,
2:30, 3:10 respectfully, or 7:35 total hours.
That is 7.58 in decimal.

Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
single SQL statement?  Or will I have to write a proc that does this?

### converting HH:MM to a decimal

> I have 3 pairs of time in/out (24 our clock) that I need to calculate
> the decimal equivalent of the total time worked for that day.

> example:  time in/out  08:00  10:00, 10:15 12:45, 13:45 16:50
>           if I use a simple select of  time_out time_ in  I get 2:00,
>           2:30, 3:10 respectfully, or 7:35 total hours.
>           That is 7.58 in decimal.

> Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
> single SQL statement?  Or will I have to write a proc that does this?

You will have to obtain or write a procedure to do it; there isn't any
way to coerce an interval into a decimal without using a stored procedure.

create procedure ivhm_decimal(i interval hour(7) to minute)
returning decimal;
define im interval minute(9) to minute;
define str char(10);
define d decimal;
let im = i;
let str = im;
let d = str / 60.0;
return d;
end procedure;

Untested.  You have to go through a string.  You might return ROUND(d,
2) to get 2 decimal places of hours.  I'm not sure about the first
assignment (let im = i); you might be able to alter the type in the
signature to interval minute(9) to minute and still invoke it with:

ivhm_decimal(t_out1 - t_in1) +
ivhm_decimal(t_out2 - t_in2) +
ivhm_decimal(t_out3 - t_in3)

Or equivalents.  Also poke around the IIUG archives - there's code
there to do the job in ESQL/C (iv_hours for your case).  It could be

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

### converting HH:MM to a decimal

>> I have 3 pairs of time in/out (24 our clock) that I need to calculate
>> the decimal equivalent of the total time worked for that day.

>> example:  time in/out  08:00  10:00, 10:15 12:45, 13:45 16:50
>>           if I use a simple select of  time_out time_ in  I get 2:00,
>>           2:30, 3:10 respectfully, or 7:35 total hours.
>> That is 7.58 in decimal.

>> Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
>> single SQL statement?  Or will I have to write a proc that does this?

> You will have to obtain or write a procedure to do it; there isn't any
> way to coerce an interval into a decimal without using a stored procedure.

Correction - a procedure is the neatest way to do it.  You should be
able to do a sequence of casts (assuming IDS 9.x) that will achieve
the same result:

ROUND((t_out1 - t_in1)::INTERVAL MINUTE(9) TO
MINUTE::CHAR(20)::DECIMAL(16) / 60.0, 2)

But having even one of those in a select statement doesn't bear much
thinking about, let alone three (or using it as an argument to SUM).

Quote:> create procedure ivhm_decimal(i interval hour(7) to minute)
>     returning decimal;
>     define im interval minute(9) to minute;
>     define str char(10);
>     define d decimal;
>     let im = i;
>     let str = im;
>     let d = str / 60.0;
>     return d;
> end procedure;

> Untested.  You have to go through a string.  You might return ROUND(d,
> 2) to get 2 decimal places of hours.  I'm not sure about the first
> assignment (let im = i); [...]

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Just store the date with time in the table and create the date w/o time when
you need it.

I would like to convert mm/dd/yyyy hh:mm:ss AM/PM format into mm/dd/yyyy
format
within the same table.  So I can have 'date w/ time' field and 'date w/o
time'