| Arithmatic Operation on Time [message #437070] |
Wed, 30 December 2009 21:06  |
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
|
Senior Member |
|
|
Dear fellow,
I have 3 text items for shift timestart, timeoff and
timeconsumed. All 3 items are date datatype and i have given
them HH:MI AM format Mask and we are using 12 Hours day.
The requirement is when user enter timestart as 0910, timeoff as
0200 then a display item should show the time consumed i.e.
timeoff - timestart.
in Key-Next-item trigger when i write
timeconsumed := timestart - timeoff
and compile it generates error 'expression is of wrong type'.
Would you please help me that how we can use 12 hours format and how can perform arithmatic operation on 2 date datatype specially using time?
Please help me. As i have a little bit experience of oracle but trying to learn and develop some usefull database.
Muhammad Khalil
[Updated on: Wed, 30 December 2009 21:08] Report message to a moderator |
|
|
|
| Re: Arithmatic Operation on Time [message #437075 is a reply to message #437070] |
Wed, 30 December 2009 22:32  |
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
|
Junior Member |
|
|
Hi,
"date - date" will return a number. So TimeConsumed cannot be a date datatype. The return value will be a decimal number. A simple search should return several solutions on how to convert this decimal number into some meaningful interval value.
-- Balaji
|
|
|
|
|
|
| Re: Arithmatic Operation on Time [message #437151 is a reply to message #437102] |
Thu, 31 December 2009 04:45 |
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
|
Senior Member |
|
|
Again i would like to repeat my question and assignment statement.
Would you please help me that how we can use 12 hours format
and how can perform arithmatic operation on 2 date datatype
specially using time?
:Timeconsumed := :timeoff - :timestart;
While compiling the above statement the error
'expression is of wrong type' show with :timeoff.
Muhammad Khalil
[Updated on: Thu, 31 December 2009 04:46] Report message to a moderator |
|
|
|
| Re: Arithmatic Operation on Time [message #437162 is a reply to message #437151] |
Thu, 31 December 2009 06:44 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What exactly did you not understand of what has already been said?
Date (or "time", although that is still the DATE dataatype, never mind the format you chose) subtraction returns a number (number of days, actually), not a date. Therefore, you'll need to convert that number into format you are interested in.
This job requires some calculation ("how to convert a decimal number (of days) into hours:minutes format"?", but that's not really a Forms problem. Perhaps you should search SQL & PL/SQL Forum for some examples, if you are uncertain of how to do that. The basics are simple: 1 day = 24 hours, 1 hour = 60 minutes, etc.
Now it is your turn.
|
|
|
|
| Re: Arithmatic Operation on Time [message #437207 is a reply to message #437162] |
Fri, 01 January 2010 05:45 |
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
|
Senior Member |
|
|
Thanks Littlefoot for your detailed and meaningfull elaboration.
I did the job as: timeconsumed Varchar2, Timestart Date, Timeoff
Date. Now write the statment.
:timeconsumed := to_char(:timeoff,'HH:MI AM') - to_char(:timestart,'HH:MI AM');
The timestart and timeoff have format mask HH:MI AM. When i enter the data as 09:10 AM for timestart and 11:00 AM for timeoff the result which display in timeconsumed textitem is ######## where the length of timeconsumed is 10 characters. Where is the problem please guide me.
Muhammad Khalil
[Updated on: Fri, 01 January 2010 05:49] Report message to a moderator |
|
|
|
| Re: Arithmatic Operation on Time [message #437208 is a reply to message #437207] |
Fri, 01 January 2010 06:20 |
|
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You didn't get it, I'm afraid. You can't subtract CHARACTERS if you want to get the result; subtract DATES, and adjust the output.
Here's an SQL*Plus example (dates are DD.MM.YYYY HH24:MI):SQL> desc test
Name Null? Type
-------------------------------------------------- -------- --------------
TIMESTART DATE
TIMEOFF DATE
TIMECONSUMED DATE
SQL> l
1 select
2 timestart,
3 timeoff,
4 -- difference is number of days
5 timeoff - timestart no_days,
6 -- number of hours = no_days * 24
7 (timeoff - timestart) * 24 no_hours_and_min,
8 -- remove minutes from hours
9 trunc((timeoff - timestart) * 24) no_hours,
10 -- number of minutes = the rest of hours * 60
11 (((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60 no_minutes
12* from test
SQL> /
TIMESTART TIMEOFF NO_DAYS NO_HOURS_AND_MIN NO_HOURS NO_MINUTES
---------------- ---------------- ---------- ---------------- ---------- ----------
01.01.2010 08:20 01.01.2010 10:25 .086805556 2.08333333 2 5
See? All you have to do is subtract and multiply with certain figures (as I've said, 1 day = 24 hours, 1 hour = 60 minutes, ...). The result you need is NO_HOURS and NO_MINUTES (2 hours 5 minutes).
To do that, apply TO_CHAR to format '2' and '5' to '02:05':SQL> select
2 lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') no_hours,
3 lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0') no_minutes
4 from test;
NO_HOURS NO_MINUT
-------- --------
02 05
SQL> select
2 lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
3 lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0') hours_minutes
4 from test;
HOURS_MINUTES
----------------
0205
SQL> select
2 to_date(
3 lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
4 lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0'),
5 'hh24:mi'
6 ) hours_minutes
7 from test;
HOURS_MINUTES
----------------
01.01.2010 02:05
OK; let's now update the table:SQL> update test set
2 timeconsumed =
3 to_date(
4 lpad(to_char(trunc((timeoff - timestart) * 24)), 2, '0') ||
5 lpad(to_char(round((((timeoff - timestart) * 24) - trunc((timeoff - timestart) * 24)) * 60)), 2, '0')
6 'hh24:mi'
7 );
1 row updated.
Finally, the result, selected with TO_CHAR to display it as you'd want it to:
SQL> select timestart, timeoff, to_char(timeconsumed, 'hh24:mi') cons
2 from test;
TIMESTART TIMEOFF CONS
---------------- ---------------- -----
01.01.2010 08:20 01.01.2010 10:25 02:05 |
|
|
|
| Re: Arithmatic Operation on Time [message #437278 is a reply to message #437208] |
Sat, 02 January 2010 22:52 |
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
|
Senior Member |
|
|
Dear Littlefoot,
Sonice of you. So sweet. Thanks a lot. Now it is clear and i
hope that my problem of date operation have been solved for ever
because i have got the concept with your guidance and so
detailed solution. Thanks once again.
Muhammad Khalil
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
