Home » Developer & Programmer » Forms » converting numbers to hours...helpppp!!
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



Re: converting numbers to hours...helpppp!! [message #221161 is a reply to message #221160] 
Sat, 24 February 2007 10:02 
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 backend..



Re: converting numbers to hours...helpppp!! [message #221310 is a reply to message #221161] 
Mon, 26 February 2007 04:14 

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_leftv_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 
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






Goto Forum:
Current Time: Fri Jan 20 14:19:51 CST 2017
Total time taken to generate the page: 0.13828 seconds
