problem in time manipulation in query.. [message #269458] |
Sat, 22 September 2007 07:50  |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
Hello all,
---> my table is c_emplogdetail which is having two fields
login date;
logout date;
which are to note the login and logout time..
--->To find the total working time by using following query :
select
to_char( to_date('00:00:00','HH24:MI:SS') +
(LOGIN + LOGOUT), 'HH24:MI:SS')
from c_emplogdetail;
This query gives me proper result i.e. total time of that employee at the office..
----> Now, the rule of company is minimum 9 hrs and 20 minutes each employee shold b there in company..
I am trying to desing the query which returns the extra of less time the emp is there in company...
i.e. suppose if, employee is there in company for 10 hrs then result should be 40 minutes
if, employee is there in company for 9 hrs then result should be -20 minutes
and if, employee is there in company for 11 hrs then result should be 1 hr 40 minutes i.e. 01:40:00..
i am trying following query...
select to_char(to_date('09:20:00','HH24:MI:SS') -
to_date('00:00:00','HH24:MI:SS') +
(logout - login), 'HH24:MI:SS')
from c_emplogdetail;
But this query is giving error :
ORA-01481 : Invalid number format model
Please help me to get my objective....
Note : The oracle is installed in other pc than the one having net.. so i cannot give you the copied work from the sql plus..
Thanking you....
|
|
|
|
Re: problem in time manipulation in query.. [message #269465 is a reply to message #269462] |
Sat, 22 September 2007 08:16   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
i am trying following query...
select to_char(to_date('09:20:00','HH24:MI:SS') -
to_date('00:00:00','HH24:MI:SS') +
(logout - login), 'HH24:MI:SS')
from c_emplogdetail;
But this query is giving error :
ORA-01481 : Invalid number format model
|
|
|
|
|
|
Re: problem in time manipulation in query.. [message #269532 is a reply to message #269526] |
Sun, 23 September 2007 02:18   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
hello gurus,
select to_date('09:20:00','HH24:MI:SS') from dual;
this query is executed properly without any error (i.e. no number format error..)
But when i m using that thing.....
"to_date('09:20:00','HH24:MI:SS')"
in my query, it's giving error...
I am not getting what is actually wrong??
I have also tried to use anonymous pl/sql block where i had tried to use differnt way for same thing but it is also giving the same error..
Please let me know where i am wrong....
Thanking you,
Dipali
|
|
|
|
|
Re: problem in time manipulation in query.. [message #269537 is a reply to message #269534] |
Sun, 23 September 2007 02:36   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
dear anacedent,
nonsense????
I have tried myself to do this work..
now at this step i am confused, not able to slove it myself and so looking for the help...
I was not expecting such word from a senior member like you..
I am a fan of our community (orafaq) and respect each senior members like you, taj, michel, frank , harshad and all...
Whenever i know, i always tried to help others to solve their problem..
But anyways, may b you are so busy and so react like that....
Regards....
Dipali
|
|
|
Re: problem in time manipulation in query.. [message #269538 is a reply to message #269537] |
Sun, 23 September 2007 02:44   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
okay michel..
i will try to explain what result actually i want to have, and in how i am trying to do that....
The company's minimum hours is 9:20 hrs...
I find the total time spent by an employee at company by using following query..
select
to_char( to_date('00:00:00','HH24:MI:SS') +
(LOGIN - LOGOUT), 'HH24:MI:SS')
from c_emplogdetail;
This is working fine..
now, i want the difference between the company' decided working hours i.e. 9:20 hrs and the employee's worked hours...
for that i tried following query....
select to_char(to_date('09:20:00','HH24:MI:SS') -
to_date('00:00:00','HH24:MI:SS') +
(logout - login), 'HH24:MI:SS')
from c_emplogdetail;
But this query is giving error :
ORA-01481 : Invalid number format model
Error is at line 4 near, 'HH24:MI:SS'...
But, i can't find further way to proceed..
Please let me know what i am missing..?
[Updated on: Sun, 23 September 2007 02:46] Report message to a moderator
|
|
|
|
Re: problem in time manipulation in query.. [message #269541 is a reply to message #269539] |
Sun, 23 September 2007 03:15   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
SQL> select
2 to_char( to_date('00:00:00','HH24:MI:SS') +
3 (LOGOUT-LOGIN), 'HH24:MI:SS')
4 from emplogdetail;
TO_CHAR(
--------
00:33:39
00:00:00
SQL> select
2 to_date('09:20:00','HH24:MI:SS') - to_date('00:00:00','HH24:MI:SS') + (LOGOUT-LOGIN)
3 from emplogdetail;
TO_DATE('09:20:00','HH24:MI:SS')-TO_DATE('00:00:00','HH24:MI:SS')+(LOGOUT-LOGIN)
--------------------------------------------------------------------------------
.412256944
2.38888889
SQL> select
2 to_char(to_date('09:20:00','HH24:MI:SS') -
3 to_date('00:00:00','HH24:MI:SS') +
4 (LOGOUT-LOGIN), 'HH24:MI:SS')
5 from emplogdetail;
(LOGOUT-LOGIN), 'HH24:MI:SS')
*
ERROR at line 4:
ORA-01481: invalid number format model
|
|
|
|
|
Re: problem in time manipulation in query.. [message #269561 is a reply to message #269537] |
Sun, 23 September 2007 08:38   |
mson77
Messages: 208 Registered: August 2007 Location: Brazil
|
Senior Member |
|
|
Hello vithalani_dipali,
anacedent wrote: | >to_char(to_date('09:20:00','HH24:MI:SS') - to_date('00:00:00','HH24:MI:SS') + (logout - login), 'HH24:MI:SS')
HUH?
a TO_CHAR minus a TO_DATE plus ??????
What nonsense is this conglomeration of different datatypes?
|
and...
vithalani_dipali wrote | dear anacedent,
nonsense????
I have tried myself to do this work..
now at this step i am confused, not able to slove it myself and so looking for the help...
I was not expecting such word from a senior member like you..
I am a fan of our community (orafaq) and respect each senior members like you, taj, michel, frank , harshad and all...
Whenever i know, i always tried to help others to solve their problem..
But anyways, may b you are so busy and so react like that....
Regards....
Dipali
|
For me... anacedent comment does not sound negative.
If I were you I would thank anacedent because she is asking yourself for independence... tips are not answers.
And now with this your message:
vithalani_dipali wrote | ya.. we got the clue..
and we are(me and my brother) now trying to solve it by hook or cook....
Thanks for the concern Michel..
Thanks a lot...
Regards ..
Michel...
|
Congratulations... but if I were you... I would realize myself and thank to everyone including anacedent. Also I would post the correct sql statement here for the others in the future.
All of these comments above are just my personal opinion. Just my thought.
Maybe this a clue also. A clue for life.
Regards,
mson77
|
|
|
|
|