OraFAQ Forum: Forms » converting numbers to hours...helpppp!!

Home » Developer & Programmer » Forms » converting numbers to hours...helpppp!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

converting numbers to hours...helpppp!! [message #221160]

Sat, 24 February 2007 09:32

sophia_786
Messages: 13
Registered: February 2007
Location: manchester

Junior Member

Hi all,

please can u help in this little problem...

i am creating a timesheet application using oracle forms6i,
i want the user to be able to enter the number of hours worked on a daily basis.

how do i convert the number entered to equal hours...

for example

monday project 1 = 5.30 hours
monday project 2 = 1.30 hours

to equal the amount of 7 hours?? then for this to be displayed in a text field?

thanks in advance

x

Report message to a moderator

Re: converting numbers to hours...helpppp!! [message #221161 is a reply to message #221160]

Sat, 24 February 2007 10:02

Frank
Messages: 7901
Registered: March 2000

Senior Member

Are you sure you want the user to enter 5.3 hours? 5.5 or separate fields for hours and minutes seems more intuitive to me.
If you stick to your solution, you have to take your decimal part of each value (so PRIOR to adding) and divide that by 60.
Quite a nuisance, both on the input side and on the back-end..

Report message to a moderator

Re: converting numbers to hours...helpppp!! [message #221310 is a reply to message #221161]

Mon, 26 February 2007 04:14

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,

If you convert numbers to hours, then 5.30 is not equal to 5 hours and 30 minutes instead it is 5 hours and 18 minutes.
1.50 is equal to 1 hour and 30 minutes.

Here's a function to change the numbers into hours and minutes.

CREATE OR REPLACE FUNCTION get_time_from_number(p_number IN NUMBER)
RETURN VARCHAR2 IS
v_time VARCHAR2(10);
v_left VARCHAR2(3);
v_right VARCHAr2(2);
BEGIN
 IF INSTR(p_number, '.' ) = 0 THEN
   IF LENGTH(p_number) = 1 THEN
    v_time := '0'||p_number||':00';
   ELSIF LENGTH(p_number) > 1 THEN
    v_time := p_number||':00';
   END IF;
 ELSIF INSTR(p_number, '.' ) = 1 THEN
  v_time := '00:'||ROUND(SUBSTR(p_number, INSTR(p_number, '.')) * 60);
 ELSIF INSTR(p_number, '.' ) = 2 THEN
  v_time := '0'||floor(p_number)||':'||ROUND(SUBSTR(p_number, INSTR(p_number, '.')) * 60);
 ELSIF INSTR(p_number, '.' ) > 2 THEN
  v_time := floor(p_number)||':'||ROUND(SUBSTR(p_number, INSTR(p_number, '.')) * 60);
 ELSE
  v_time := '00:00';
 END IF;
 IF LENGTH(v_time) < 5 THEN
  v_left := SUBSTR(v_time, 1, 3);
  v_right := SUBSTR(v_time,4);
  v_right := '0'||v_right;
  v_time := v_left||v_right;
 END IF;
 RETURN v_time;
END;
/

By using this function, we can get

SQL> SELECT Get_Time_From_Number(5.30)
  2  FROM dual;

GET_TIME_FROM_NUMBER(5.30)
----------------------------------------------------------
05:18

SQL> SELECT Get_Time_From_Number(1.5)
  2  FROM dual;

GET_TIME_FROM_NUMBER(1.5)
----------------------------------------------------------
01:30

SQL> SELECT Get_Time_From_Number(5.30 + 1.50)
  2  FROM dual;

GET_TIME_FROM_NUMBER(5.30+1.50)
--------------------------------------------------
06:48

hope this will help out.

regards,
Saadat Ahmad

Report message to a moderator

Re: converting numbers to hours...helpppp!! [message #221315 is a reply to message #221310]

Mon, 26 February 2007 04:24

Frank
Messages: 7901
Registered: March 2000

Senior Member

Wouldn't this be simpler then your function?

SQL> select trunc(&a)||':'||round((&a - trunc(&a)) * 60) from dual;
Enter value for a: 1.5
Enter value for a: 1.5
Enter value for a: 1.5
old   1: select trunc(&a)||':'||round((&a - trunc(&a)) * 60) from dual
new   1: select trunc(1.5)||':'||round((1.5 - trunc(1.5)) * 60) from dual

TRUN
----
1:30

But then again, it is NOT what the OP asked for..

[Updated on: Mon, 26 February 2007 04:24]

Report message to a moderator