Home » Developer & Programmer » Forms » converting numbers to hours...helpppp!!
converting numbers to hours...helpppp!! [message #221160] Sat, 24 February 2007 09:32 Go to next message
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
Re: converting numbers to hours...helpppp!! [message #221161 is a reply to message #221160] Sat, 24 February 2007 10:02 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: converting numbers to hours...helpppp!! [message #221310 is a reply to message #221161] Mon, 26 February 2007 04:14 Go to previous messageGo to next message
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
Re: converting numbers to hours...helpppp!! [message #221315 is a reply to message #221310] Mon, 26 February 2007 04:24 Go to previous messageGo to next message
Frank
Messages: 7880
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

Re: converting numbers to hours...helpppp!! [message #221329 is a reply to message #221315] Mon, 26 February 2007 06:00 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

agreed.
Re: converting numbers to hours...helpppp!! [message #221356 is a reply to message #221315] Mon, 26 February 2007 07:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Frank
Wouldn't this be simpler ...
And, then again, wouldn't use of && be simpler tha use of &? ./fa/1704/0/
Re: converting numbers to hours...helpppp!! [message #221364 is a reply to message #221356] Mon, 26 February 2007 08:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, but that would make a reuse within the same sql-session more difficult..


*Ahem*wiseguy**Ahem*

Wink
Previous Topic: simple lov
Next Topic: ORA-00918: column ambiguously defined
Goto Forum:
  


Current Time: Sat Dec 10 10:59:38 CST 2016

Total time taken to generate the page: 0.22447 seconds