| time in oracle form 10g [message #582484] |
Thu, 18 April 2013 13:08  |
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
|
Senior Member |
|
|
i have two field one is start_time and end_time to enter the task start_time and end_time.
Now whats is datatype is should keep in oracle form and what at the database feild.
i want enter 09:30 start_time and 12:30 to end_time and in third field the difference of these two fields.
|
|
|
|
| Re: time in oracle form 10g [message #582495 is a reply to message #582484] |
Thu, 18 April 2013 15:35  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
DATE column, DATETIME text item property with proper format mask.
Different between 2 DATEs is number of days.
Fairly simple calculation to get hours, minutes and seconds.
|
|
|
|
| Re: time in oracle form 10g [message #582501 is a reply to message #582495] |
Thu, 18 April 2013 21:09 |
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
|
Senior Member |
|
|
|
but my requirement is to calculate the time not the days...it means that if start time is 9 am and end time is 12 pm then the its result will be 12-9=3 but when i do it ...it subract data from date...not time how??
|
|
|
|
|
|
| Re: time in oracle form 10g [message #582608 is a reply to message #582507] |
Sat, 20 April 2013 01:46 |
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
|
Senior Member |
|
|
i have two fields thats is data type of datetime one for the start_time and other is for the
End_time...i enter the time for 9:30 and 12:0 for two fields but when i going to substract end_time from start_time these substarct the date from each other not the time...because both the field is enter on the same date therefore the result return to 0....
|
|
|
|
|
|
| Re: time in oracle form 10g [message #582669 is a reply to message #582631] |
Sun, 21 April 2013 01:30 |
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
|
Senior Member |
|
|
declare
v_start_time number;
begin
select (to_date(mt.start_time,'dd-mm-yyyy hh:mi')- to_date(mt.end_time,'dd-mm-yyyy hh:mi')) into v_start_time
from TASK_MT mt
where mt.time_id=:time_id;
Message('Start time :'||v_start_time);
Message('');
exception
when others then
Message('error is :'||dbms_error_text);
end;
this is return 0 whether start_time is 9:30 and end-time is 12:30
The Result i needed is 3 ...End_time - Start_time
|
|
|
|
|
|
| Re: time in oracle form 10g [message #582803 is a reply to message #582669] |
Mon, 22 April 2013 12:58 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
irfankundi786@yahoo.com wrote on Sun, 21 April 2013 02:30
select (to_date(mt.start_time,'dd-mm-yyyy hh:mi')- to_date(mt.end_time,'dd-mm-yyyy hh:mi')) into v_start_time
It looks like you are using TO_DATE on a DATE column. This is incorrect.
Additionally, how does subtracting an end time from a start time give you a start time, unless your columns and variables are not well named.
|
|
|
|
| Re: time in oracle form 10g [message #582982 is a reply to message #582803] |
Wed, 24 April 2013 05:20 |
 |
siddiqui_88
Messages: 12
Registered: April 2013
Location: lahore
|
Junior Member |
|
|
select substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) outtime,substr(to_char(to_date('23-MAR-2013 090000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) intime,
lpad(substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6)-substr(to_char(to_date('23-MAR-2013 090000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6),6,'0') ramain_time
from dual
this will help you dear!
|
|
|
|
| Re: time in oracle form 10g [message #582983 is a reply to message #582982] |
Wed, 24 April 2013 05:52 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No it won't help. First off your code is hard to read - Please read and follow How to use [code] tags and make your code easier to read?
Secondly, and more importantly, your code doesn't work. If we change the start time to 9:59:00 we get:
SQL> select substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) outtime,
substr(to_char(to_date('23-MAR-2013 095900' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6) intime,
2 3 lpad(substr(to_char(to_date('23-MAR-2013 120000' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6)
4 -substr(to_char(to_date('23-MAR-2013 095900' ,'dd-mon-rrrr hh24miss'),'ddmonrrrrhh24miss'),-6),6,'0') ramain_time
5 from dual;
OUTTIME INTIME RAMAIN_TIME
------------------ ------------------ ------------------
120000 095900 024100
That should be 2 hours 1 minute, not 2 hours 41 minutes.
Littlefoot has shown how it should be done above.
|
|
|
|
| Re: time in oracle form 10g [message #582987 is a reply to message #582982] |
Wed, 24 April 2013 07:46 |
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
|
Senior Member |
|
|
irfan ahmad • one another problem i am facing regarding to this post....
i have three fields start_time , End_time And Difference_time means end_time - start_time
now i enter 5 or 6 enteries like
start_time end_time Difference_time
9:00 12:00 3:00
14:00 15:00 1:00
15:00 16:30 1:30
at the end of block i want to sum all defference_time field to calculate all time worked.
now the defference_time is the time datatype having formate mast 24hh:mi
how this can be possible to sum this field??
|
|
|
|
|
|
| Re: time in oracle form 10g [message #583045 is a reply to message #583035] |
Thu, 25 April 2013 02:42 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@siddiqui_88 - A simple summary column will suffer the exact same problem as your previous attempt at a solution - it'll assume there are 100 minutes in an hour instead of 60.
@irfankundi786@yahoo.com
This is simple maths:
1) add the hours together.
2) Add the minutes togther.
3) Divide minutes by 60 and floor the result to get extra hours
4) Add extra hours to hours total
5) Subtract extra hours * 60 from minutes total to get remaining minutes.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
