Home » Developer & Programmer » Forms » converting numbers to hours...helpppp!!
converting numbers to hours...helpppp!! Sat, 24 February 2007 09:32
 sophia_786 Messages: 13Registered: 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?

x
Re: converting numbers to hours...helpppp!! [message #221161 is a reply to message #221160] Sat, 24 February 2007 10:02
 Frank Messages: 7880Registered: 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
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,
Re: converting numbers to hours...helpppp!! [message #221315 is a reply to message #221310] Mon, 26 February 2007 04:24
 Frank Messages: 7880Registered: 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
agreed.
Re: converting numbers to hours...helpppp!! [message #221356 is a reply to message #221315] Mon, 26 February 2007 07:54
 Littlefoot Messages: 21151Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
 Frank Wouldn't this be simpler ...
And, then again, wouldn't use of && be simpler tha use of &?
Re: converting numbers to hours...helpppp!! [message #221364 is a reply to message #221356] Mon, 26 February 2007 08:10
 Frank Messages: 7880Registered: March 2000 Senior Member
Yes, but that would make a reuse within the same sql-session more difficult..

*Ahem*wiseguy**Ahem*

 Previous Topic: insertion of multiple list items into table in forms6i Next Topic: last record.......
Goto Forum:

Current Time: Thu Aug 24 00:07:18 CDT 2017

Total time taken to generate the page: 0.15995 seconds