|
|
|
|
|
Re: help me please [message #627317 is a reply to message #627316] |
Mon, 10 November 2014 16:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1f86adf76089b51356cd53e5795a3367?s=64&d=mm&r=g) |
xebec
Messages: 37 Registered: July 2014 Location: Miraflores
|
Member |
|
|
For Example:
Date Finished : 04/05/2014
Hour Finished : 12:30
Date Started : 03/05/2014
Hour Started : 12:00
The Different between Finished - Started is = Estimated (in Hours)
Estimated : 24:30
[Updated on: Mon, 10 November 2014 16:12] Report message to a moderator
|
|
|
Re: help me please [message #627321 is a reply to message #627315] |
Mon, 10 November 2014 16:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1229663c86eb1a441385fe710cd9116e?s=64&d=mm&r=g) |
mughals_king
Messages: 392 Registered: January 2012 Location: pakistan
|
Senior Member |
|
|
i don't think i have completely understand your question i tried to READ your uploaded image but i could not understand due to language anywayz i can give you some examples regarding time calculation may be could help you otherwise sorry
SELECT * FROM test;
TIME1 TIME2
--------- ---------
27-OCT-14 17-OCT-14
01-OCT-14 01-OCT-14
Elapsed: 00:00:00.00
SELECT TO_CHAR(time1, 'DD-MON-YYYY HH:MI:SS') dt1, TO_CHAR(time2, 'DD-MON-YYYY HH:MI:SS') time2,
(time1 - time2) * 24 difference
FROM test
DT1 TIME2 DIFFERENCE
-------------------- -------------------- ---------------
27-OCT-2014 12:00:00 17-OCT-2014 12:00:00 240
01-OCT-2014 12:33:00 01-OCT-2014 10:33:00 2
--------------------------2nd Example---------------------------------------------
Select floor((dt1-dt2)*24*60*60)/3600)||'HOURS'||floor((((dt1-dt2)*24*60*60)
-
floor(((dt1-dt2)*24*60*60)/3600)*3600)/60)||'MINUTES'||round((((dt1-dt2)*24*60*60)
-
floor(((dt1-dt2)*24*60*60)/3600)*3600 -
(floor((((dt1-dt2)*24*60*60) -
floor(((dt1-dt2)*24*60*60)/3600)*3600)/60)*60)))||'SECS' time_difference from dual;
-------------------------3rd Example-------------------------------
DECLARE
DH NUMBER;
DM NUMBER;
DS NUMBER;
DMD1 CHAR(2);
DMD2 CHAR(2);
BEGIN
DH:=SUBSTR(:OUT_TIME,1,2)-SUBSTR(:IN_TIME,1,2);
DM:=SUBSTR(:OUT_TIME,4,2)-SUBSTR(:IN_TIME,4,2);
DS:=SUBSTR(:OUT_TIME,7,2)-SUBSTR(:IN_TIME,7,2);
DMD1:=SUBSTR(:IN_TIME,10,2);
DMD2:=SUBSTR(:OUT_TIME,10,2);
IF DMD1=DMD2 THEN
:FINAL:=DH||':'||DM||':'||DS;
ELSE
:FINAL:=DH+12||':'||DM||':'||DS;
END IF;
END;
Regards
Mughal
[Updated on: Mon, 10 November 2014 17:04] Report message to a moderator
|
|
|
|
|
|
Re: help me please [message #627345 is a reply to message #627325] |
Tue, 11 November 2014 02:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
OK, you are nearly there. You have the difference in units of days:
1.04166666666666666666666666666666666667
The number to the left of the decimal is the difference in whole days. Take the remainder (using the MOD function may be useful), multiply by 24, and you have the whole hours - with a remainder. Multiply that remainder by 60, to obtain the minutes. And so on.
Incidentally, when you reply please follow standard forum etiquette: do not post in upper case, and use the best English that you can. DNT DO THS PLZ.
[Updated on: Tue, 11 November 2014 02:40] Report message to a moderator
|
|
|
|
|
Re: help me please [message #627453 is a reply to message #627389] |
Wed, 12 November 2014 02:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You used floor on the hours to avoid decimals, did it not occur to you to try using floor on the minutes and seconds as well?
|
|
|
Re: help me please [message #627469 is a reply to message #627453] |
Wed, 12 November 2014 03:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Also why is there a select statement in this code?
And why are you converting a date to a char and then back again?
|
|
|
|
Re: help me please [message #627534 is a reply to message #627532] |
Wed, 12 November 2014 11:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1f86adf76089b51356cd53e5795a3367?s=64&d=mm&r=g) |
xebec
Messages: 37 Registered: July 2014 Location: Miraflores
|
Member |
|
|
MAN!!!!
OH MY GOD!!!
I THINK THATA GOD IS NOT HELPING ME!! T.T
NOW MY CODE IS WRONG ....
IT SHOWS ME AN ERROR: ORA-01403
IF I DON'T MISTAKE , IT'S ABOUT MISSING FIELD, ISN'T IT?
BUT I FILL ALL FIELDS ABOUT TIME
DATESTART
HOURSTART
DATEFINISH
HOURFINISH
MY CODE IN THE TRIGGER : WHEN-MOUSE-CLIC IS :
DECLARE
RESULT NUMBER;
STARTDATE DATE;
FINISHDATE DATE;
X VARCHAR2(100);
Y VARCHAR2(100);
M1 VARCHAR2(100);
BEGIN
X := to_char(:RGSFECHAFINPDF,'DD/MM/YYYY')||' '||to_char(:RGSHRAFINPDF,'HH24:MI');
Y := to_char(:RGSFECHAINIPDF,'DD/MM/YYYY')||' '||to_char(:RGSHRAINIPDF,'HH24:MI');
FINISHDATE := to_date(X,'DD/MM/YYYY HH24:MI');
STARTDATE := to_date(Y,'DD/MM/YYYY HH24:MI');
SELECT floor((FINISHDATE - STARTDATE)*24)||':'||
ROUND((MOD((FINISHDATE - STARTDATE)*1440,60)),2)||':'||
ROUND((MOD((FINISHDATE - STARTDATE)*86400,60)),2)
INTO :RGSESTIMADOPDF FROM RGSORDMNT;
/*EXCEPTION
WHEN OTHERS THEN
MESSAGE('SOMETHING IS MISSING... TRY AGAIN');*/
END;
I NEED SOME EXPERT IN ORACLE FORM PLEASE!!!
IT IS AN EMERGENCY PLEASE!!!!
https://imageshack.com/i/idSDTrB1j
http://imageshack.com/a/img661/7792/SDTrB1.jpg
|
|
|
Re: help me please [message #627548 is a reply to message #627534] |
Wed, 12 November 2014 12:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
First, we need to talk a little bit about etiquette. All CAPS in a post is the equivalent of SHOUTING or YELLING! So, you are YELLING and the people you would like to have help you. Really?
Secondly,
Quote:I NEED SOME EXPERT IN ORACLE FORM PLEASE!!!
IT IS AN EMERGENCY PLEASE!!!!
You realize that we are all volunteers right? If it is that urgent, contact Oracle Support and pay them to help you!
I'm going to echo cookiemonsters statement - why do you have a SELECT in your code? You already have the data values you need to perform your calculation - there is no need to go to the database for anything. You have a very simply calculate and you have made it very complicated. You code should be as simple as:
BEGIN
:YOUR_BLOCK.ESTIMATED := ROUND(24 *
to_date(to_char(:YOUR_BLOCK.DATE_STARTED||' '||:YOUR_BLOCK.HOUR_STATED,'MM-DD-YYYY HH24:mi'),'MM-DD-YYYY HH24:mi')
- to_date(to_char(:YOUR_BLOCK.DATE_ENDED||' '||:YOUR_BLOCK.HOUR_ENDED,'MM-DD-YYYY HH24:mi'),'MM-DD-YYYY HH24:mi')
);
END;
Did you try searching the Internet for how to calculate the difference in hours between two dates in Oracle?
Craig...
[Updated on: Wed, 12 November 2014 12:17] Report message to a moderator
|
|
|
Re: help me please [message #627557 is a reply to message #627548] |
Wed, 12 November 2014 13:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1f86adf76089b51356cd53e5795a3367?s=64&d=mm&r=g) |
xebec
Messages: 37 Registered: July 2014 Location: Miraflores
|
Member |
|
|
Ohh man!
Sorry, ALL CAPS was activated by accident.
I would like to apologize to everyone at the forum by this little moment.
Specially:
@Craig
@mughals_king
@John Watson
@cookiemonster
I understand the hard work you are doing, managing the forum.
I said that it's an emergency, because it's my work :'(
My boss told me that If I don't finish this.
probably they are not going to renew my contract for the next few months.
Please brothers!! ![Sad](images/smiley_icons/icon_sad.gif)
I beg you.
Help me!
[Updated on: Wed, 12 November 2014 13:37] Report message to a moderator
|
|
|
Re: help me please [message #627584 is a reply to message #627557] |
Wed, 12 November 2014 18:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/1f86adf76089b51356cd53e5795a3367?s=64&d=mm&r=g) |
xebec
Messages: 37 Registered: July 2014 Location: Miraflores
|
Member |
|
|
AEWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
Thanks You so much my friends!!!
All of you give me excelent ideas!! ![Very Happy](images/smiley_icons/icon_biggrin.gif)
But It's done!!!!
I finish it!! ![Very Happy](images/smiley_icons/icon_biggrin.gif)
The code it's the same:
BEGIN
DECLARE
RESULT NUMBER;
STARTDATE DATE;
FINISHDATE DATE;
STARTDATE2 DATE;
FINISHDATE2 DATE;
STARTDATE3 DATE;
FINISHDATE3 DATE;
X VARCHAR2 (100);
Y VARCHAR2 (100);
SUBTRACTED_DATE DATE;
BEGIN
X := to_char(:RGSFECHAFINPDF,'DD/MM/YYYY')||' '|| to_char(:RGSHRAFINPDF,'HH24:MI');
Y := to_char(:RGSFECHAINIPDF,'DD/MM/YYYY')||' '|| to_char(:RGSHRAINIPDF,'HH24:MI');
FINISHDATE := to_date(x,'DD/MM/YYYY HH24:MI');
STARTDATE := to_date(y,'DD/MM/YYYY HH24:MI');
SELECT FLOOR ((FINISHDATE-STARTDATE)*24)|| ':' ||round((MOD((FINISHDATE-STARTDATE) * 1440,60)),0)|| ':' ||
round((MOD((FINISHDATE-STARTDATE)*86400,60)),0) INTO :RGSESTIMADOPDF FROM RGSORDMNT
where
RGSCODCIA = SUBSTR(:CIASUC,1,3) and
RGSCODSUC = SUBSTR(:CIASUC,4,3) and
RGSNUMSOL = :RGSNUMSOL;
Commit;
EXCEPTION
WHEN OTHERS THEN
MESSAGE ('RECUERDA, PRIMERO SE GRABA Y DESPUES DE CALCULA O DEBE FALTARTE ALGUN CAMPO FECHA U HORA DE PADIF');
END;
I just put this code into the boton : Calculate Time. and Commit! ![Very Happy](images/smiley_icons/icon_biggrin.gif)
Thanks you so much my friends!
|
|
|
Re: help me please [message #627616 is a reply to message #627584] |
Thu, 13 November 2014 02:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I still don't see any reason for the select. You've got the values you need to compare in the form, there is absolutely no need to go to the DB.
And you should use floor in all cases, not round, otherwise 30 mins and 45 seconds will be displayed as 31 mins and 45 seconds
Should be this:
:RGSESTIMADOPDF := FLOOR ((FINISHDATE-STARTDATE)*24)|| ':' ||floor((MOD((FINISHDATE-STARTDATE) * 1440,60)),0)|| ':' ||
floor((MOD((FINISHDATE-STARTDATE)*86400,60)),0);
|
|
|