Home » SQL & PL/SQL » SQL & PL/SQL » problem in time manipulation in query..
problem in time manipulation in query.. [message #269458] Sat, 22 September 2007 07:50 Go to next message
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 #269462 is a reply to message #269458] Sat, 22 September 2007 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More than 100 post and you still didn't read the guidelines nor know how to format your post.

You are out for me. I didn't even read what's inside the post.

Regards
Michel
Re: problem in time manipulation in query.. [message #269465 is a reply to message #269462] Sat, 22 September 2007 08:16 Go to previous messageGo to next message
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 #269467 is a reply to message #269465] Sat, 22 September 2007 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'HH24:MI:SS' is not a number format model.
Read and try to understand what you wrote.

Regards
Michel
Re: problem in time manipulation in query.. [message #269495 is a reply to message #269458] Sat, 22 September 2007 23:20 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Read the format models
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
Re: problem in time manipulation in query.. [message #269526 is a reply to message #269495] Sun, 23 September 2007 01:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Sun, 23 September 2007 06:20
Read the format models
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

The original poster doesn't use this. He seems to be well aware of the format models; it's just that he expects a date where he gets a number.
Re: problem in time manipulation in query.. [message #269532 is a reply to message #269526] Sun, 23 September 2007 02:18 Go to previous messageGo to next message
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 #269533 is a reply to message #269458] Sun, 23 September 2007 02:22 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>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?
Re: problem in time manipulation in query.. [message #269534 is a reply to message #269532] Sun, 23 September 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
this query is executed properly without any error (i.e. no number format error..)

So this is not the root of your error. You made one step, go on.
Explain us your query. Explain what it does step by step.

Regards
Michel

Re: problem in time manipulation in query.. [message #269537 is a reply to message #269534] Sun, 23 September 2007 02:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #269539 is a reply to message #269538] Sun, 23 September 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer these questions:
  • why the first query works?
  • what is the type of the difference between 2 dates
  • Are you sure you are doing the correct way, I mean with the correct datatypes?

Regards
Michel
Re: problem in time manipulation in query.. [message #269541 is a reply to message #269539] Sun, 23 September 2007 03:15 Go to previous messageGo to next message
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 #269542 is a reply to message #269541] Sun, 23 September 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't ask you queries.
I asked you to EXPLAIN with words.

By the way, don't the 2 last queries give you a clue what's wrong?

Regards
Michel
Re: problem in time manipulation in query.. [message #269547 is a reply to message #269542] Sun, 23 September 2007 04:19 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

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...
Re: problem in time manipulation in query.. [message #269561 is a reply to message #269537] Sun, 23 September 2007 08:38 Go to previous messageGo to next message
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
Re: problem in time manipulation in query.. [message #269617 is a reply to message #269458] Sun, 23 September 2007 21:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Dipali, if I ask to calculate what is the result of 'a'-2 . Can you do? You can't because the first one is in character format and the 2nd one is in number format.

Similarly you want to subtract date format(to_date) data type from character (to_char)format data type. It is not possible.

I have not seen any problem when you got success in difference between
two dates.
Re: problem in time manipulation in query.. [message #269653 is a reply to message #269617] Mon, 24 September 2007 00:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The problem is not in the subtraction, it is in the format model of the to_char
Previous Topic: fucntion/view/design question
Next Topic: Exceptions no_data
Goto Forum:
  


Current Time: Fri Dec 02 20:35:03 CST 2016

Total time taken to generate the page: 0.24523 seconds